title: SQL: Grouped average-if / case SELECT statement
body: <p>I have a database that looks like this SQL Fiddle: <a href="http://sqlfiddle.com/#!9/aa02e/1" rel="nofollow">http://sqlfiddle.com/#!9/aa02e/1</a></p>
  
  <pre><code>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 |
  +-------+------------+---------+---------+-------+---------+
  </code></pre>
  
  <p>It shows revenue data for stores incl. products, date and the respective day/month.
  I want to select the following:</p>
  
  <ul>
  <li>Store</li>
  <li>Monthly revenue totals (i.e. what is the total revenue for store a in Jan?)</li>
  <li>Weekday revenue averages (i.e. what is the avg revenue for store a on Thu?)</li>
  </ul>
  
  <p>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:</p>
  
  <ul>
  <li>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)</li>
  <li>Take the average of the two values (e.g. avg(16.5,4)=10.25)</li>
  </ul>
  
  <p>How can I accomplish that?
  Thank you</p>
  
  <p>Here is the query:</p>
  
  <pre><code>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
  ;
  </code></pre>