Return sums even if no data exist in given day

Question

I have a table with amounts only on some days, e.g.:

[DATE] [AMT]
11/1/2017 $123
11/1/2017 $50
11/3/2017 $123

How can I query the data and get:

11/1/2017 $173
11/2/2017 $0
11/3/2017 $123

I tried like:

SELECT Day([Date]) AS [Day], Nz(Sum(AMT),0) AS [Day Total]
FROM mytable
WHERE Month([Date])=11
GROUP BY Day([Date]);

But still it doesn't return 0s for days without data, any ideas?

Answer 1

You need a table with all the days, so you can fill the holes, lets call it allDays

Get a list of dates between two dates using a function

Then your query is:

 SELECT a.[date], COALESCE(SUM([AMT]),0)
 FROM allDates a
 LEFT JOIN yourTable t
   ON a.[date] = t.[date]
 GROUP BY a.[date]

Answer 2

In Access, you can create a series of dates between the first and the last date, and then create an outer join to your summed data.

Here is how to create the date series:

Create a date table between two dates

Answer 3

There are two simple solutions.

• Join to a table with all dates in the month, as in Juan's answer.
• Use this query by user RedFilter to generate the dates.

Answer 4

I suggest you try the following query:

SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total
FROM table
WHERE date <= NOW()
and date >= Date_add(Now(),interval - 12 month)
GROUP BY DATE_FORMAT(date, "%m-%Y"))