CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

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

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...

Upvotes: 0

Views: 58

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

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

Upvotes: 1

Related Questions