title: MySQL day-on-day totals by day of the week
body: <p>My shopping cart revenue appears to vary by day of the week, with Monday and Thursday being low days, Wednesday and Saturday being high days. I would therefore like to display a web page showing totals like this:</p>
  
  <pre><code>Week #    Sun    Mon    Tue    Wed    Thu    Fri    Sat
  Week 1   $5.00  $1.00  $3.00  $9.00  $1.00  $3.00  $9.00
  Week 2   $5.23  $1.07  $2.98  $8.75  $0.02  $3.14  $7.51
  Week 3   etc.
  </code></pre>
  
  <p>I can query for a given day of the week like this:</p>
  
  <pre><code>SELECT count( id ) AS orders, 
         order_date, 
         date_format( order_date, '%a' ) AS weekday, 
         sum( total) AS revenue
  FROM `ss_orders`
  WHERE dayofweek( order_date ) = 1
        AND order_date &gt;= date_add( now( ) , INTERVAL -83 DAY )
  GROUP BY order_date
  ORDER BY order_date DESC
  </code></pre>
  
  <p>which gives me the daily totals for all Sundays in the past 12 weeks. I can therefore do 7 queries to get what I need (1 query for each day of the week). It seems like I ought to be able to get the whole thing in a single query. </p>
  
  <p>What should the query be? Thanks!</p>
  
  <p>EDIT: Here is the corrected query from the preferred solution.</p>
  
  <pre><code>SELECT 
    week( o.order_date ) as WkNumber,
    sum( if( weekday( o.order_date ) = 6, 1, 0 ) * o.total ) as SalesSun,
    sum( if( weekday( o.order_date ) = 6, 1, 0 )) as OrdersSun,
    sum( if( weekday( o.order_date ) = 0, 1, 0 ) * o.total ) as SalesMon,
    sum( if( weekday( o.order_date ) = 0, 1, 0 )) as OrdersMon,
    sum( if( weekday( o.order_date ) = 1, 1, 0 ) * o.total ) as SalesTue,
    sum( if( weekday( o.order_date ) = 1, 1, 0 )) as OrdersTue,
    sum( if( weekday( o.order_date ) = 2, 1, 0 ) * o.total ) as SalesWed,
    sum( if( weekday( o.order_date ) = 2, 1, 0 )) as OrdersWed,
    sum( if( weekday( o.order_date ) = 3, 1, 0 ) * o.total ) as SalesThu,
    sum( if( weekday( o.order_date ) = 3, 1, 0 )) as OrdersThu,
    sum( if( weekday( o.order_date ) = 4, 1, 0 ) * o.total ) as SalesFri,
    sum( if( weekday( o.order_date ) = 4, 1, 0 )) as OrdersFri,
    sum( if( weekday( o.order_date ) = 5, 1, 0 ) * o.total ) as SalesSat,
    sum( if( weekday( o.order_date ) = 5, 1, 0 )) as OrdersSat,
    sum( o.total ) as SalesWeek,
    sum( 1 ) as OrdersWeek
  from
    ss_orders o
  where
    o.order_date &gt; date_add( now(), INTERVAL -13 WEEK )
  group by
    week( o.order_date )
  order by o.order_date desc
  </code></pre>