Finding an average number of transactions in a day by year

Question

I have a table of orders for an epos system.

I have several years worth of data and I'd like to find out the average number of transactions per day of the week each year. i.e. which is the busiest day of the week for 2014 vs 2013 etc.

I can get the info out this way:

SELECT ROUND(AVG(sales),1) AS dayaverage, oday, oyear FROM (
SELECT COUNT(oid) AS sales, DAYNAME(odate) AS oday , YEAR(odate) AS oyear
FROM orders 
WHERE ostatus = 'completed'
GROUP BY DATE(odate)
ORDER BY YEAR(odate), DAYOFWEEK(odate)) t1
GROUP BY oday, oyear
ORDER BY oyear DESC, AVG(sales) DESC

Which yeilds the data a want, but not quite in the right format.

I get this:

"dayaverage"    "oday"      "oyear"
"28.9"          "Saturday"  "2015"
"17.1"          "Sunday"    "2015"
"15.0"          "Tuesday"   "2015"
"14.3"          "Monday"    "2015"
"13.1"          "Wednesday" "2015"
"13.0"          "Friday"    "2015"

I have created this statement:

SELECT tsun.pyear, sunday,monday,tuesday, wednesday, thursday, friday, saturday FROM (
SELECT ROUND(AVG(payments),1) AS Sunday, pyear FROM (
SELECT COUNT(paymentid) AS payments, YEAR(DATETIME) AS pyear
FROM payments 
WHERE DAYNAME(DATETIME) = "Sunday"
GROUP BY DATE(DATETIME)
ORDER BY YEAR(DATETIME), DAYOFWEEK(DATETIME)) t1
GROUP BY pyear
ORDER BY pyear DESC, AVG(payments) DESC) AS tsun
LEFT JOIN (SELECT * FROM (SELECT ROUND(AVG(payments),1) AS Monday, pyear FROM (
SELECT COUNT(paymentid) AS payments, YEAR(DATETIME) AS pyear
FROM payments 
WHERE DAYNAME(DATETIME) = "Monday"
GROUP BY DATE(DATETIME)
ORDER BY YEAR(DATETIME), DAYOFWEEK(DATETIME)) t2
GROUP BY pyear
ORDER BY pyear DESC, AVG(payments) DESC) AS tmon) tmon2 ON tsun.pyear = tmon2.pyear
LEFT JOIN (SELECT * FROM (SELECT ROUND(AVG(payments),1) AS Tuesday, pyear FROM (
SELECT COUNT(paymentid) AS payments, YEAR(DATETIME) AS pyear
FROM payments 
WHERE DAYNAME(DATETIME) = "Tuesday"
GROUP BY DATE(DATETIME)
ORDER BY YEAR(DATETIME), DAYOFWEEK(DATETIME)) t2
GROUP BY pyear
ORDER BY pyear DESC, AVG(payments) DESC) AS ttue) ttue2 ON tsun.pyear = ttue2.pyear
LEFT JOIN (SELECT * FROM (SELECT ROUND(AVG(payments),1) AS Wednesday, pyear FROM (
SELECT COUNT(paymentid) AS payments, YEAR(DATETIME) AS pyear
FROM payments 
WHERE DAYNAME(DATETIME) = "Wednesday"
GROUP BY DATE(DATETIME)
ORDER BY YEAR(DATETIME), DAYOFWEEK(DATETIME)) t2
GROUP BY pyear
ORDER BY pyear DESC, AVG(payments) DESC) AS twed) twed2 ON tsun.pyear = twed2.pyear
LEFT JOIN (SELECT * FROM (SELECT ROUND(AVG(payments),1) AS Thursday, pyear FROM (
SELECT COUNT(paymentid) AS payments, YEAR(DATETIME) AS pyear
FROM payments 
WHERE DAYNAME(DATETIME) = "Thursday"
GROUP BY DATE(DATETIME)
ORDER BY YEAR(DATETIME), DAYOFWEEK(DATETIME)) t2
GROUP BY pyear
ORDER BY pyear DESC, AVG(payments) DESC) AS tthu) tthu2 ON tsun.pyear = tthu2.pyear
LEFT JOIN (SELECT * FROM (SELECT ROUND(AVG(payments),1) AS Friday, pyear FROM (
SELECT COUNT(paymentid) AS payments, YEAR(DATETIME) AS pyear
FROM payments 
WHERE DAYNAME(DATETIME) = "Friday"
GROUP BY DATE(DATETIME)
ORDER BY YEAR(DATETIME), DAYOFWEEK(DATETIME)) t2
GROUP BY pyear
ORDER BY pyear DESC, AVG(payments) DESC) AS tfri) tfri2 ON tsun.pyear = tfri2.pyear
LEFT JOIN (SELECT * FROM (SELECT ROUND(AVG(payments),1) AS Saturday, pyear FROM (
SELECT COUNT(paymentid) AS payments, YEAR(DATETIME) AS pyear
FROM payments 
WHERE DAYNAME(DATETIME) = "Saturday"
GROUP BY DATE(DATETIME)
ORDER BY YEAR(DATETIME), DAYOFWEEK(DATETIME)) t2
GROUP BY pyear
ORDER BY pyear DESC, AVG(payments) DESC) AS tsat) tsat2 ON tsun.pyear = tsat2.pyear

Which also yields the data I want in the format I want;

"pyear" "sunday"    "monday"    "tuesday"   "wednesday" "thursday"  "friday"    "saturday"
"2015"  "18.4"  "13.0"  "16.5"  "14.3"  "13.3"  "17.3"  "31.3"
"2014"  "17.8"  "19.0"  "17.6"  "15.3"  "15.5"  "20.2"  "32.0"
"2013"  "3.4"   "3.9"   "3.4"   "3.7"   "3.4"   "4.5"   "6.2"
"2012"  "2.8"   "4.8"   "4.7"   "4.8"   "3.7"   "5.7"   "7.1"
"2011"  "4.0"   "7.1"   "5.6"   "6.2"   "6.6"   "5.4"   "6.2"
"2010"  "3.0"   "5.5"   "5.7"   "5.2"   "5.3"   "4.6"   "6.6"
"2009"  "2.5"   "4.3"   "3.5"   "4.9"   "4.8"   "2.9"   "3.9"

But it's not very elegant is it!

What I would like to know is if there is a better way to do it?

Is there something like the first statement that will yield something like the 2nd one?

I'm sure there is a clever way to do it, like using a case statement but the solution escapes me at the moment. Anyone got any ideas?

Thanks in advance,

Marc

---

So I got to this, thanks Gordon.

SELECT oyear,
       ROUND(AVG(CASE WHEN oday = 'Monday' THEN sales END), 1) AS Mon,
       ROUND(AVG(CASE WHEN oday = 'Tuesday' THEN sales END), 1) AS Tue,
       ROUND(AVG(CASE WHEN oday = 'Wednesday' THEN sales END), 1) AS Wed,
       ROUND(AVG(CASE WHEN oday = 'Thursday' THEN sales END), 1) AS Thu,
       ROUND(AVG(CASE WHEN oday = 'Friday' THEN sales END), 1) AS Fri,
       ROUND(AVG(CASE WHEN oday = 'Saturday' THEN sales END), 1) AS Sat,
       ROUND(AVG(CASE WHEN oday = 'Sunday' THEN sales END), 1) AS Sun
FROM (SELECT COUNT(oid) AS sales, DAYNAME(odate) AS oday , YEAR(odate) AS oyear
      FROM orders 
      WHERE ostatus = 'completed'
      GROUP BY DATE(odate)
     ) d
GROUP BY oyear
ORDER BY oyear ASC

Answer 1

Use conditional aggregation:

SELECT ROUND(AVG(sales),1) AS dayaverage, oday, oyear,
       ROUND(AVG(CASE WHEN oday = 'Monday' THEN sales END), 1) as Mon,
       ROUND(AVG(CASE WHEN oday = 'Tuesday' THEN sales END), 1) as Tue,
       ROUND(AVG(CASE WHEN oday = 'Wednesday' THEN sales END), 1) as Wed,
       ROUND(AVG(CASE WHEN oday = 'Thursday' THEN sales END), 1) as Thu,
       ROUND(AVG(CASE WHEN oday = 'Friday' THEN sales END), 1) as Fri,
       ROUND(AVG(CASE WHEN oday = 'Saturday' THEN sales END), 1) as Sat,
       ROUND(AVG(CASE WHEN oday = 'Sun' THEN sales END), 1) as Sun
FROM (SELECT COUNT(oid) AS sales, DAYNAME(odate) AS oday , YEAR(odate) AS oyear
      FROM orders 
      WHERE ostatus = 'completed'
      GROUP BY DATE(odate)
     ) d
GROUP BY oyear
ORDER BY DESC;