CODEWITHSUNDEEP
google-bigquery
Pratikp
Pratikp

Reputation: 31

Aggregate By month from date data in Big Query

I have a data table with three columns -

Date, Order Amount, Branch id

Date Format in the date column - yyyy-mm-dd 00:00:00

I want the information to be aggregated in MM.YY format.

I tried format_date and group by functions, but unable to run the code. Any help would be highly appreciated.

Upvotes: 0

Views: 615

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Consider below option/example

SELECT BranchID, 
  FORMAT_DATE('%m.%y', DATE(Date)) AS mmyy, 
  SUM(OrderAmount) AS OrderAmounts
FROM sample
GROUP BY 1, 2

Upvotes: 0

Jaytiger
Jaytiger

Reputation: 12254

Try this one assuming that Date column has a date-formatted string.

WITH sample AS (
  SELECT '2022-05-22 00:00:00' AS `Date`, 100 AS OrderAmount, 1 AS BranchID
   UNION ALL
  SELECT '2022-05-21 00:00:00' AS `Date`, 200 AS OrderAmount, 1 AS BranchID
   UNION ALL
  SELECT '2022-04-22 00:00:00' AS `Date`, 150 AS OrderAmount, 2 AS BranchID
   UNION ALL
  SELECT '2022-04-21 00:00:00' AS `Date`, 250 AS OrderAmount, 2 AS BranchID
)
SELECT BranchID, FORMAT_DATE('%m.%y', DATE(LEFT(`Date`, 10))) AS mmyy, SUM(OrderAmount) OrderAmounts
  FROM sample
 GROUP BY 1, 2
;

output:

enter image description here

Upvotes: 1

Related Questions