-1

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   |
| --------------- | -------------- |
| max@gmail.com   |  2021-10-01    |
| emma@gmail.com  |  2021-10-05    |
| tony@gmail.com  |  2021-12-06    |
| mark@gmail.com  |  2022-01-23    |
| lucy@gmail.com  |  2022-01-28    |
| chris@gmail.com |  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!

Amberjack
  • 45
  • 5
  • 1
    In these cases that are very occuring in BI problems, it is best if you create a dates table having the date (YYYY-MM-DD) as a unique column. Then you would left join to your facts query or table. Similar to the first answer posted here – Fnaxiom Mar 10 '22 at 17:01
  • Thank you. Now I understand how to proceed. – Amberjack Mar 10 '22 at 18:42

1 Answers1

2

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.

mbd
  • 346
  • 1
  • 9