[T-SQL] Summing values by month, and returning 0 for months with no records?
Let's say I have the following really simple table:
| Date | Amount |
|---|---|
| 1/1/16 | 100 |
If I were to run the following query:
SELECT Month([Date]), SUM([Amount]) FROM Table GROUP BY Month([Date])
I'd get the following results:
| Month | Sum of Amount |
|---|---|
| 1 | 100 |
But what I really want are my results to look like this:
| Month | Amount |
|---|---|
| 1 | 100 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
| 10 | 0 |
| 11 | 0 |
| 12 | 0 |
How do I tell the query to return a result for all values in MONTH = 1-12
Posted Jan 5, 2017, 11:00 PM UTC
25