Group by month with "virtual" month to get full year

Question

I have a table with accounts and subscription amount and more or less the following query :

SELECT FORMAT_DATE(\"%Y-%m\", DATE(receivedOn)) AS Date,
       SUM(amount)
FROM `mytablehere`
GROUP BY Date
ORDER BY Date ASC

The output is fine however some month don't have any subscription so amount is null and the month isn't returned in the final result. Since I'm directly importing the result in a Google Sheet to build a chart from it, not having the full 12 months year is annoying.

Is there a way to generate a "virtual" month for missing month ? Answer to a similar question suggested to create another table with full month and joint the two tables... I'm looking for something simpler...

Create a calendar table containing all months. Do an outer join. — jarlh, Apr 18 '18 at 08:15
Can you take a look at this answer, it seems to address the same concern. https://stackoverflow.com/questions/34179398/how-do-i-create-a-list-of-dates-before-doing-a-left-join — Krishnan Venkiteswaran, Apr 18 '18 at 08:19
The exact script for creating a calendar should be something like the one below, you can easily adapt it to any db https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql — Krishnan Venkiteswaran, Apr 18 '18 at 08:23

Mikhail Berlyant · Accepted Answer · 2018-04-18T10:56:00.567

Is there a way to generate a "virtual" month for missing month ?

Below example is for BigQuery Standard SQL

#standardSQL
WITH `mytablehere` AS (
  SELECT '2018-01-01' AS receivedOn, 1 amount UNION ALL
  SELECT '2018-01-02' AS receivedOn, 2 amount UNION ALL
  SELECT '2018-03-04' AS receivedOn, 4 amount UNION ALL
  SELECT '2018-05-10' AS receivedOn, 3 amount UNION ALL
  SELECT '2018-05-11', 5
), months AS (
  SELECT month FROM (
    SELECT 
      MIN(PARSE_DATE('%Y-%m-%d', receivedOn)) AS min_month, 
      MAX(PARSE_DATE('%Y-%m-%d', receivedOn)) AS max_month 
    FROM `mytablehere`
  ), UNNEST(GENERATE_DATE_ARRAY(min_month, max_month, INTERVAL 1 MONTH)) month
)
SELECT 
  FORMAT_DATE('%Y-%m', month) AS DATE,
  SUM(amount) amount
FROM months
LEFT JOIN `mytablehere`
ON FORMAT_DATE('%Y-%m', PARSE_DATE('%Y-%m-%d', receivedOn)) = FORMAT_DATE('%Y-%m', month)
GROUP BY DATE
ORDER BY DATE ASC

with result as

Row DATE    amount   
1   2018-01 3    
2   2018-02 null     
3   2018-03 4    
4   2018-04 null     
5   2018-05 8

Group by month with "virtual" month to get full year

1 Answers1