Report Fields

I am having problem combining multiplie quries into one

SELECT username, count(*) as SuccessfulTotal, sum(point) as SuccessfulPoints FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400 AND status = 1 group by username

SELECT count(*) as Return FROM sales where submit_date >= 1301612400 AND submit_date <= 1304204400 AND status = 2  group by username

So in the report table I will have 3 fields

Username | SuccessfulTotal | SuccessfulPoints | Return

Shouldnt it be…

SELECT COUNT(s1.point) AS return, COUNT(s2.point) AS SuccessfulTotal, SUM(s2.point) AS SuccessfulPoints 
FROM sales AS s1 
INNER JOIN sales AS s2 ON s1.username = s2.username 
WHERE submit_date >= 1301612400 
AND submit_date <= 1304204400 
AND s1.status = 2 
AND s2.status = 1  
GROUP BY username <Probably should have an ORDER BY in here too somewhere...>

?

(My MySQL brain isnt firing too well today apparently, so i’ll expect r937 to come along in a second and correct me ;P)

Thank you :slight_smile:

I think there is a mistake in your SQL?

status = 2 show number of sales returns total. Not the point.

status = 1 show numbers of sales and points.

SELECT username
     , SUM(CASE WHEN status = 1
                THEN 1
                ELSE NULL END) as SuccessfulTotal
     , SUM(CASE WHEN status = 1
                THEN point
                ELSE NULL END) as SuccessfulPoints 
     , SUM(CASE WHEN status = 2
                THEN 1
                ELSE NULL END) as Return
  FROM sales 
 WHERE submit_date BETWEEN 1301612400 AND 1304204400 
GROUP 
    BY username

thanks :slight_smile: Working great…

Is there an alternative way too?

:slight_smile:

yeah, but it’s way more complex :slight_smile:

How do you feel about this:

SELECT 
   distinct(outer_sales.username), 
   (SELECT count(*) as Points FROM sales where status = 1 AND username = outer_sales.username) as TotalSales,
   (SELECT sum(point) as Points FROM sales where status = 1 AND username = outer_sales.username) as Points,
   (SELECT count(*) FROM sales where status = 2 AND username = outer_sales.username) as Return,
   (SELECT count(*) FROM sales where (status = 3 OR status = 6) AND username = outer_sales.username) as UnsuccessfulSales
FROM 
   sales outer_sales
ORDER BY
   outer_sales.username;

nauseous :goof: