CODEWITHSUNDEEP
mysql
Edward Barnard
Edward Barnard

Reputation: 356

MySQL day-on-day totals by day of the week

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:

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.

I can query for a given day of the week like this:

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 >= date_add( now( ) , INTERVAL -83 DAY )
GROUP BY order_date
ORDER BY order_date DESC

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.

What should the query be? Thanks!

EDIT: Here is the corrected query from the preferred solution.

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 > date_add( now(), INTERVAL -13 WEEK )
group by
  week( o.order_date )
order by o.order_date desc

Upvotes: 1

Views: 1551

Answers (2)

DRapp
DRapp

Reputation: 48179

you are actually looking for a type of pivot table query.

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 > date_add( now(), INTERVAL -12 WEEK )
   group by
      week( o.order_date )

you might have to adjust the WHERE clause to truly get 12 full weeks if you are in the middle of a week... such as changing NOW() to something like date_add( now(), interval -weekday(now()) ), but I'd have to think more on that part.

Upvotes: 0

dognose
dognose

Reputation: 20909

Group by week then day?

SELECT count( id ) AS orders, 
       date_format( order_date, '%a' ) AS weekday, 
       WEEK(order_date) AS week_number,
       sum( total) AS revenue
FROM 
       `ss_orders`
WHERE 
       order_date >= date_add( now( ) , INTERVAL -83 DAY )
GROUP BY 
       WEEK(orderdate), DAY(orderdata)
ORDER BY 
       order_date DESC

Should give you a result like this:

orders | weekday | week_number | revenue
5        Sun       1             20
6        Mon       1             15
...

Upvotes: 2

Related Questions