retrojacket retrojacket - 7 months ago 18
SQL Question

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?

Answer
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