Reputation: 356
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
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
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