I have a query like this:
SELECT SUM(spend.price) AS total, WEEKDAY(spend.date) AS weekday
FROM spend
WHERE spend.date BETWEEN '2012-11-01' AND '2012-11-07'
GROUP BY WEEKDAY(spend.date) ;
Right now the query gives me only the days in which the user had some payments.
total weekday
60 2
10 3
3 5
3 6
But instead I want to give me 0 (zeros) for every day without payments.
total weekday
0 1
60 2
10 3
0 4
3 5
3 6
0 7
Table schema here.
Here is the original syntax:
SELECT SUM(spend.price) AS total, day AS weekday
FROM (SELECT price, WEEKDAY(date) as day FROM spend WHERE date BETWEEN '2012-11-01' AND '2012-11-07'
UNION ALL
SELECT 0,0
UNION ALL
SELECT 0,1
UNION ALL
SELECT 0,2
UNION ALL
SELECT 0,3
UNION ALL
SELECT 0,4
UNION ALL
SELECT 0,5
UNION ALL
SELECT 0,6
) spend
GROUP BY day ;
And here is the shortened one:
CREATE TEMPORARY TABLE tmp ( `price` int, `day` int );
INSERT INTO tmp VALUES (0,0),(0,1),(0,2),(0,3),(0,4),(0,5),(0,6);
SELECT SUM(spend.price) AS total, day AS weekday
FROM (SELECT price, WEEKDAY(date) as day FROM spend WHERE date BETWEEN '2012-11-01' AND '2012-11-07'
UNION ALL
SELECT * FROM tmp
) spend
GROUP BY day ;
Here is a demonstration: http://sqlfiddle.com/#!2/8c323/3/0