retrojacket - 11 months ago 106

SQL Question

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