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
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)
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
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;