CODEWITHSUNDEEP
phpsql
user1419810
user1419810

Reputation: 846

SQL to return 0 with no data

I have an SQL query (as follows) in a PHP script that runs to return total values by day of week for a given week number:

SELECT order_day, SUM(order_total)
FROM $table_name
WHERE order_week_number = '45'
GROUP BY order_day
ORDER BY order_day_num;

This provides the data I need however does not display a 0 for days with not data, for example the following is outputted when there is no data for either Sunday or Monday:

DOW     Total Order Value
Tuesday   - $1857.5
Wednesday - $1314.5
Thursday  - $1279
Friday    - $2526.5
Saturday  - $1866.5

Is there an easy way to show 0 for days with no data?

Upvotes: 0

Views: 92

Answers (1)

Harsh
Harsh

Reputation: 1319

You can use UNION as below, though beware that it is untested code:

SELECT T.order_day, SUM(COALESCE(foo.order_total,0)) FROM 
(
SELECT 'Monday' AS day UNION ALL
SELECT 'Tuesday' UNION ALL
SELECT 'Wednesday' UNION ALL
SELECT 'Thursday' UNION ALL
SELECT 'Friday' UNION ALL
SELECT 'Saturday' UNION ALL
SELECT 'Sunday' 
) AS T LEFT JOIN $table_name AS foo 
ON T.day = foo.order_day
WHERE foo.order_week_number = '45' GROUP BY T.order_day ORDER BY foo.order_day_num

Upvotes: 1

Related Questions