How to include null values into grouping by date?-CodePudding

I'd like to split number of emails added to the database by month and year.

My query:

    SELECT TOP 200 Monthh, Yearr, COUNT(Email) AS Amount
    FROM
    (SELECT Email, MONTH(Added_date) AS Monthh, YEAR(Added_date) AS Yearr
    FROM Contacts) a
    GROUP BY Monthh, Yearr
    ORDER BY CAST(Yearr AS INT), CAST(Monthh AS INT)

But let's say that my (very simplified) contact list looks like this:

|     Email       |   Added_date   |
| --------------- | -------------- |
| [email protected]   |  2021-10-01    |
| [email protected]  |  2021-10-05    |
| [email protected]  |  2021-12-06    |
| [email protected]  |  2022-01-23    |
| [email protected]  |  2022-01-28    |
| [email protected] |  2022-02-04    |

In this case, the result would look like this:

| Yearr | Monthh | Amount |
| ----- | ------ | ------ |
| 2021  |   10   |   2    |
| 2021  |   12   |   1    |
| 2022  |   01   |   2    |
| 2022  |   02   |   1    |

This works. However, there's a slight issue - November 2021 is not returned (which is of course highly logical), but I'd like to return it anyway in my result with null (0, zero etc.) value.

My dream result would be this:

| Yearr | Monthh | Amount |
| ----- | ------ | ------ |
| 2021  |   10   |   2    |
| 2021  |   11   |   0    |
| 2021  |   12   |   1    |
| 2022  |   01   |   2    |
| 2022  |   02   |   1    |

I can't seem to find an easy solution. And you can easily that the real split needs to be done back to year 2000.

Btw, I am running this query in SOQL and BigQuery if it's important.

Hope it's all clear and thank you for your help!

CodePudding user response：

I think one of the easiest ways would be to create 2 auxiliar tables, one with the years you want to get info from (2000-current year), and another one with months (1-12), so you could perform an outer join with your actual table and get the number of mails created by year-month.

Let's say table years is called Years_Table with year_value column and Months', Months_table with month_value column, then you could do

SELECT TOP 200 month_value, year_value, COUNT(Email) AS Amount
FROM Contacts 
RIGHT OUTER JOIN (SELECT year_value, month_value FROM Years_Table CROSS JOIN Months_Table) AS AUX_TABLE ON AUX_TABLE.year_value = YEAR(Added_Date) AND AUX_TABLE.month_value = MONTH(Added_Date)
GROUP BY month_value, year_value
ORDER BY year_value, month_value

Note: I ommited your CAST instruction since Year(added_date) should be a numeric, asuming your field added_date is a datetime field, on the contrary you should perform a different join.