CODEWITHSUNDEEP
sqlgoogle-bigquery
marin
marin

Reputation: 953

Proportion of data per month

I'm trying to make a table of data proportion per month

Data:

Message | Date |

bla bla | 2018-04-10 |

blabla | 2018-04-11 |

blabla | 2018-04-12 |

blabla | 2018-04-13 |

blabla | 2018-05-13 |

blabla | 2018-05-14 |

blabla | 2018-05-15 |

blabla | 2018-05-16 |

blabla | 2018-05-17 |

I tried

SELECT Date, count(*) as Message, EXTRACT(YEAR FROM Date) as year, EXTRACT(MONTH FROM Date) as month, MIN(Date) as date
FROM `my_table` 
GROUP BY Date, EXTRACT(YEAR FROM Date) as an, EXTRACT(MONTH FROM Date)

I wanted:

Message | Date |

4 | april |

5 | may |

I'm so sorry, I don't know how to put tables here.

Upvotes: 0

Views: 46

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL 

#standardSQL
SELECT 
  COUNT(*) AS Message, 
  EXTRACT(YEAR FROM Date) AS year, 
  FORMAT_DATE('%B', Date) AS month
FROM `project.dataset.my_table` 
GROUP BY 
  EXTRACT(YEAR FROM Date), 
  FORMAT_DATE('%B', Date)

You can test, play with above using dummy data fro you question as below 

#standardSQL
WITH `project.dataset.my_table` AS (
  SELECT 'bla bla' Message, DATE '2018-04-10' Date UNION ALL
  SELECT 'blabla', '2018-04-11' UNION ALL
  SELECT 'blabla', '2018-04-12' UNION ALL
  SELECT 'blabla', '2018-04-13' UNION ALL
  SELECT 'blabla', '2018-05-13' UNION ALL
  SELECT 'blabla', '2018-05-14' UNION ALL
  SELECT 'blabla', '2018-05-15' UNION ALL
  SELECT 'blabla', '2018-05-16' UNION ALL
  SELECT 'blabla', '2018-05-17' 
)
SELECT 
  COUNT(*) AS Message, 
  EXTRACT(YEAR FROM Date) AS year, 
  FORMAT_DATE('%B', Date) AS month
FROM `project.dataset.my_table` 
GROUP BY 
  EXTRACT(YEAR FROM Date), 
  FORMAT_DATE('%B', Date)

with result as 

Row Message year    month    
1   5       2018    May  
2   4       2018    April

Upvotes: 2

kiran gadhe
kiran gadhe

Reputation: 743

If data is limited to this year only then 

 SELECT EXTRACT(MONTH FROM Date) date , count(*) message from your_table 
 GROUP BY EXTRACT(MONTH FROM Date)

For multiple year you can try 

 SELECT EXTRACT(MONTH FROM Date) as Mnth,EXTRACT(YEAR FROM Date) as yr ,
 count(*) message from your_table 
 GROUP BY EXTRACT(MONTH FROM Date), EXTRACT(YEAR FROM Date)

Upvotes: 2

Related Questions