Berbatov Berbatov - 3 months ago 19
SQL Question

SQL: Grouped average-if / case SELECT statement

I have a database that looks like this SQL Fiddle: http://sqlfiddle.com/#!9/aa02e/1

CREATE TABLE Table1
(`Store` varchar(1), `Date` date, `Product` varchar(2), `Weekday` int, `Month` int, `Revenue` float)
;

INSERT INTO Table1
(`Store`, `Date`, `Product`, `Weekday`, `Month`, `Revenue`)
VALUES
('a', '20160101', 'aa', 5, 1, 1.5),
('a', '20160101', 'bb', 5, 1, 4),
('a', '20160101', 'cc', 5, 1, 3.5),
('a', '20160108', 'dd', 5, 1, 2.5),
('a', '20160108', 'ee', 5, 1, 5),
('b', '20160204', 'aa', 4, 2, 9.5),
('b', '20160204', 'bb', 4, 2, 4),
('b', '20160204', 'cc', 4, 2, 3),
('b', '20160211', 'dd', 4, 2, 1.5),
('b', '20160211', 'ee', 4, 2, 2.5)
;

SELECT * FROM table1;
+-------+------------+---------+---------+-------+---------+
| Store | Date | Product | Weekday | Month | Revenue |
+-------+------------+---------+---------+-------+---------+
| a | 2016-01-01 | aa | 5 | 1 | 1.5 |
| a | 2016-01-01 | bb | 5 | 1 | 4 |
| a | 2016-01-01 | cc | 5 | 1 | 3.5 |
| a | 2016-01-08 | dd | 5 | 1 | 2.5 |
| a | 2016-01-08 | ee | 5 | 1 | 5 |
| b | 2016-02-04 | aa | 4 | 2 | 9.5 |
| b | 2016-02-04 | bb | 4 | 2 | 4 |
| b | 2016-02-04 | cc | 4 | 2 | 3 |
| b | 2016-02-11 | dd | 4 | 2 | 1.5 |
| b | 2016-02-11 | ee | 4 | 2 | 2.5 |
+-------+------------+---------+---------+-------+---------+


It shows revenue data for stores incl. products, date and the respective day/month.
I want to select the following:


  • Store

  • Monthly revenue totals (i.e. what is the total revenue for store a in Jan?)

  • Weekday revenue averages (i.e. what is the avg revenue for store a on Thu?)



The first and second bullet are straightforward, but I'm having problems with the last one.
Currently, it takes the average over all products and all dates (assuming the weekday matches). What I need are the following steps:


  • Sum up all revenues for a store and a particular date (e.g. for store b: 9.5+4+3=16.5 for Feb 4th, and 1.5+2.5=4 for Feb 11th) if that date has the same weekday (here Thursday)

  • Take the average of the two values (e.g. avg(16.5,4)=10.25)



How can I accomplish that?
Thank you

Here is the query:

SELECT
Store,
SUM(CASE WHEN Month = 1 THEN Revenue ELSE NULL END) AS REVENUE_JAN,
SUM(CASE WHEN Month = 2 THEN Revenue ELSE NULL END) AS REVENUE_FEB,
AVG(CASE WHEN Weekday = 4 THEN Revenue ELSE NULL END) AS REVENUE_THU,
AVG(CASE WHEN Weekday = 5 THEN Revenue ELSE NULL END) AS REVENUE_FRI
FROM Table1
GROUP BY
Store
;

Answer

The weekday average is tricky. Your query is getting the average "order size" per weekday. But you want the total revenue.

One method is to first aggregate by weekday, but that is a bit of a mess. Instead, you can use this trick of calculating the average by dividing the total revenue by the number of days:

SELECT Store,
       SUM(CASE WHEN Month = 1 THEN Revenue ELSE NULL END) AS REVENUE_JAN,
       SUM(CASE WHEN Month = 2 THEN Revenue ELSE NULL END) AS REVENUE_FEB,
       (SUM(CASE WHEN Weekday = 4 THEN Revenue END) /
        COUNT(DISTINCT CASE WHEN Weekday = 4 THEN Date END)
       ) AS REVENUE_THU,
       (SUM(CASE WHEN Weekday = 5 THEN Revenue END) /
        COUNT(DISTINCT CASE WHEN Weekday = 5 THEN Date END)
       ) AS REVENUE_FRI
FROM Table1
GROUP BY Store;