Php – How to make MySQL return empty day when doing a group by date

mysqlphp

I have a MySQL query like this:

SELECT DAYNAME(CreatedAt) AS TheDay, SUM(Amount) AS TheSum
FROM OrderTransactions
GROUP BY TheDay

# Returns an array like:
# Array
# (
#     [0] => Array
#         (
#             [TheDay] => Thursday
#             [TheSum] => 0.02
#         )
# 
#     [1] => Array
#         (
#             [TheDay] => Wednesday
#             [TheSum] => 0.02
#         )
# 
# )

I also have a clause in there to select only transactions from the last week. If there were no transactions on certain days though, how can I still make it return a $0.00 amount for each empty day?

Best Solution

Generally the technique here is to UNION with the opposite of what you've selected.

Say you have a table daynames with the values 'Monday', 'Tuesday'... 'Sunday' in field name.

SELECT DAYNAME(CreatedAt) AS TheDay, SUM(Amount) AS TheSum
  FROM OrderTransactions
  GROUP BY TheDay
UNION
SELECT `name`, 0 FROM daynames
  WHERE daynames.name NOT IN (SELECT DISTINCT DAYNAME(CreatedAt) FROM OrderTransactions)

When you don't have date functions, you don't normally need the daynames lookup table. In this case, I don't know the shortcut to avoid it.

Related Question