CODEWITHSUNDEEP
sqlgoogle-bigquery
retrojacket
retrojacket

Reputation: 37

SQL aggregation as rows, and columns

I have the following query to Google BigQuery SQL: 

SELECT TypeOfDrink
   SUM(CASE WHEN (DAYOFWEEK(CreateAt)=1) THEN 1 ELSE 0 END) AS Sunday,
   SUM(CASE WHEN (DAYOFWEEK(CreateAt)=2) THEN 1 ELSE 0 END) AS Monday,
   SUM(CASE WHEN (DAYOFWEEK(CreateAt)=3) THEN 1 ELSE 0 END) AS Tuesday,
   SUM(CASE WHEN (DAYOFWEEK(CreateAt)=4) THEN 1 ELSE 0 END) AS Wednesday,
   SUM(CASE WHEN (DAYOFWEEK(CreateAt)=5) THEN 1 ELSE 0 END) AS Thursday,
   SUM(CASE WHEN (DAYOFWEEK(CreateAt)=6) THEN 1 ELSE 0 END) AS Friday,
   SUM(CASE WHEN (DAYOFWEEK(CreateAt)=7) THEN 1 ELSE 0 END) AS Saturday,
   COUNT(TypeOfDrink) AS AmountBought 
   FROM company_bigdata.Order 
   GROUP BY TypeOfDrink;

This is great, however I'm trying to port the data into Google Charts, and it requires the days of the weeks to be the rows, and the types of drinks to be the columns. However, my current output is this: 

Row TypeOfDrink    Sunday Monday Tuesday Wednesday Thursday Friday Saturday AmountBought     
1   Single Origin   5   31  12  25  11  17  0   101  
2   Cappuccino      35  149 130 175 153 125 41  808  
3   Caffè Mocha     24  150 161 148 176 167 6   832

However, I want the Days of the week as rows, and the TypeOfDrinks as the columns as follows: 

         Single Origin Cappuccino Caffe Mocha
Sunday    123    123       123      123  123
Monday     123   123       123      123  123
Tuesday

How would I go about doing this?

Upvotes: 1

Views: 52

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

SELECT
    DoW,
    SUM(AmountBought * (TypeOfDrink = 'Single Origin')) AS SingleOrigin,
    SUM(AmountBought * (TypeOfDrink = 'Cappuccino')) AS Cappuccino,
    SUM(AmountBought * (TypeOfDrink = 'Caffè Mocha')) AS CaffeMocha
FROM (
  SELECT 
    TypeOfDrink,
    CASE 
      WHEN (DAYOFWEEK(CreateAt)=1) THEN 'Sunday' 
      WHEN (DAYOFWEEK(CreateAt)=2) THEN 'Monday' 
      WHEN (DAYOFWEEK(CreateAt)=3) THEN 'Tuesday' 
      WHEN (DAYOFWEEK(CreateAt)=4) THEN 'Wednesday' 
      WHEN (DAYOFWEEK(CreateAt)=5) THEN 'Thursday' 
      WHEN (DAYOFWEEK(CreateAt)=6) THEN 'Friday' 
      WHEN (DAYOFWEEK(CreateAt)=7) THEN 'Saturday'
      ELSE 'UnDefined' 
    END AS DoW,
    DAYOFWEEK(CreateAt) AS pos,
    COUNT(TypeOfDrink) AS AmountBought 
  FROM company_bigdata.Order  
  GROUP BY 1, 2, 3
  ORDER BY pos
)
GROUP BY 1

Added compact version of above (skipping intermediate grouping)

SELECT 
  DAYOFWEEK(CreateAt) AS pos,
  CASE 
    WHEN (DAYOFWEEK(CreateAt)=1) THEN 'Sunday' 
    WHEN (DAYOFWEEK(CreateAt)=2) THEN 'Monday' 
    WHEN (DAYOFWEEK(CreateAt)=3) THEN 'Tuesday' 
    WHEN (DAYOFWEEK(CreateAt)=4) THEN 'Wednesday' 
    WHEN (DAYOFWEEK(CreateAt)=5) THEN 'Thursday' 
    WHEN (DAYOFWEEK(CreateAt)=6) THEN 'Friday' 
    WHEN (DAYOFWEEK(CreateAt)=7) THEN 'Saturday'
    ELSE 'UnDefined' 
  END AS DoW,
  SUM(TypeOfDrink = 'Single Origin') AS SingleOrigin,
  SUM(TypeOfDrink = 'Cappuccino') AS Cappuccino,
  SUM(TypeOfDrink = 'Caffè Mocha') AS CaffeMocha
FROM company_bigdata.Order 
GROUP BY 1, 2
ORDER BY 1

Upvotes: 1

Related Questions