MySQL - Compare data from months by day of week - e.g. sessions on first Saturday of each month

user_17108220 0
Viewed 55

I have a table recording when clients came for a session. I want to create a graph that compares how many sessions were done in the last year. I figured out how to get the results for each month with the following sql:

SELECT
  DAY(`SessionDate`) as month_day, 
  SUM(if(MONTH(SessionDate) =  1, 1, 0))  AS Jan,
  SUM(if(MONTH(SessionDate) =  2, 1, 0))  AS Feb,
  SUM(if(MONTH(SessionDate) =  3, 1, 0))  AS Mar,
  SUM(if(MONTH(SessionDate) =  4, 1, 0))  AS Apr,
  SUM(if(MONTH(SessionDate) =  5, 1, 0))  AS May,
  SUM(if(MONTH(SessionDate) =  6, 1, 0))  AS Jun,
  SUM(if(MONTH(SessionDate) =  7, 1, 0))  AS Jul,
  SUM(if(MONTH(SessionDate) =  8, 1, 0))  AS Aug,
  SUM(if(MONTH(SessionDate) =  9, 1, 0))  AS Sep,
  SUM(if(MONTH(SessionDate) = 10, 1, 0))  AS 'Oct',
  SUM(if(MONTH(SessionDate) = 11, 1, 0))  AS 'Nov',
  SUM(if(MONTH(SessionDate) = 12, 1, 0))  AS 'Dec'
FROM sessions
WHERE
`SessionDate` >= NOW()-Interval 12 MONTH
GROUP BY DAY(`SessionDate`)
ORDER BY DAY(SessionDate)

The result looks something like this:

------------------------------...
|month_day | Jan | Feb | Mar |...
------------------------------...
|         1|  1  |  2  |  0  |...
|         2|  2  |  3  |  1  |...
|         3|  0  |  4  |  1  |...
|         4|  0  |  4  |  2  |...
|         5|  4  |  3  |  3  |...
|         6|  2  |  1  |  4  |...
|         7|  2  |  0  |  4  |...
|         8|  1  |  2  |  4  |...
     ...

However, this compares every 1st of Jan to 1st of Feb to 1st of Mar... What I am trying to see and compare is how many sessions there were on the 1st Monday of every Month...

Say 1. Jan was a Friday, 1. Feb was a Tue and Mar started on a Wed then my result should be something like this:

-----------------------------...
|weekday  | Jan | Feb | Mar |...
-----------------------------...
| 1st Mon |     |     |     |...
| 1st Tue |     |  2  |     |...
| 1st Wed |     |  3  |  1  |...
| 1st Thu |     |  4  |  0  |...
| 1st Fri |  1  |  4  |  1  |...
| 1st Sat |  2  |  3  |  1  |...
| 1st Sun |  0  |  1  |  2  |...
| 2nd Mon |  0  |  0  |  3  |...
| 2nd Tue |  4  |  2  |  4  |...
| 2nd Wed |  1  |  2  |  4  |...
| 2nd Thu |  1  |  2  |  4  |...
| 2nd Fri |  3  |  3  |  2  |...
| 2nd Sat |  4  |  1  |  0  |...
| 2nd Sun |  2  |  1  |  0  |...
| 3rd Mon |  0  |  0  |  1  |...
| 3rd Tue |  1  |  0  |  4  |...
     ...      etc

In other words, the graph will display which weekdays are popular (e.g. 1st Saturday of each month has more sessions than the 3rd Saturday) I draw the graph with php and Google static charts so that's no problem. Getting the monthly data to offset so the weekdays are in the same row is what I can't figure out. Hope my question makes sense. I'm a hobby coder and new to stackoverflow so any help will be appreciated.

mysql php
1 Answers

This is the solution that I figured out. It looks a bit clunky but works. I am still open to a better solution.

    
    SELECT  
     a.m AS CalPos, /* Calendar Position (not date) 
                       e.g. if the month starts on a Tues, 
                       the 1st would display in CalPos 2 */
     COALESCE(b.Jan,0) AS 'Jan',
     COALESCE(b.Feb,0) AS 'Feb',
     COALESCE(b.Mar,0) AS 'Mar',
     COALESCE(b.Apr,0) AS 'Apr',
     COALESCE(b.May,0) AS 'May',
     COALESCE(b.Jun,0) AS 'Jun',
     COALESCE(b.Jul,0) AS 'Jul',
     COALESCE(b.Aug,0) AS 'Aug',
     COALESCE(b.Sep,0) AS 'Sep',
     COALESCE(b.Oct,0) AS 'Oct',
     COALESCE(b.Nov,0) AS 'Nov',
     COALESCE(b.Dec,0) AS 'Dec'
    FROM (
        SELECT 1 AS m 
        UNION SELECT 2 AS m 
        UNION SELECT 3 AS m 
        UNION SELECT 4 AS m 
        UNION SELECT 5 AS m 
        UNION SELECT 6 AS m 
        UNION SELECT 7 AS m 
        UNION SELECT 8 AS m 
        UNION SELECT 9 AS m 
        UNION SELECT 10 AS m 
        UNION SELECT 11 AS m 
        UNION SELECT 12 AS m
        UNION SELECT 13 AS m
        UNION SELECT 14 AS m
        UNION SELECT 15 AS m
        UNION SELECT 16 AS m
        UNION SELECT 17 AS m
        UNION SELECT 18 AS m
        UNION SELECT 19 AS m
        UNION SELECT 20 AS m
        UNION SELECT 21 AS m
        UNION SELECT 22 AS m
        UNION SELECT 23 AS m
        UNION SELECT 24 AS m
        UNION SELECT 25 AS m
        UNION SELECT 26 AS m
        UNION SELECT 27 AS m
        UNION SELECT 28 AS m
        UNION SELECT 29 AS m
        UNION SELECT 30 AS m
        UNION SELECT 31 AS m
        UNION SELECT 32 AS m
        UNION SELECT 33 AS m
        UNION SELECT 34 AS m
        UNION SELECT 35 AS m
        UNION SELECT 36 AS m
        UNION SELECT 37 AS m
        UNION SELECT 38 AS m
    ) AS a
    LEFT JOIN 
    ( SELECT 
     CASE
        WHEN
        (7 - MOD((9-DAYOFWEEK(CAST(DATE_SUB(CONCAT(DATE_FORMAT(SessionDate,'%Y-%m-'),'01'), INTERVAL 7 DAY) AS DATE) )),7)) = 7
         THEN
         (DAY(`SessionDate`)+( MOD((9-DAYOFWEEK(CAST(DATE_SUB(CONCAT(DATE_FORMAT(SessionDate,'%Y-%m-'),'01'), INTERVAL 7 DAY) AS DATE) )),7)))
        ELSE
        (DAY(`SessionDate`)+(7 - MOD((9-DAYOFWEEK(CAST(DATE_SUB(CONCAT(DATE_FORMAT(SessionDate,'%Y-%m-'),'01'), INTERVAL 7 DAY) AS DATE) )),7)))
      END AS CalPos,   
      SUM(IF(MONTH(SessionDate) =  1, 1, 0))  AS 'Jan',
      SUM(IF(MONTH(SessionDate) =  2, 1, 0))  AS 'Feb',
      SUM(IF(MONTH(SessionDate) =  3, 1, 0))  AS 'Mar',
      SUM(IF(MONTH(SessionDate) =  4, 1, 0))  AS 'Apr',
      SUM(IF(MONTH(SessionDate) =  5, 1, 0))  AS 'May',
      SUM(IF(MONTH(SessionDate) =  6, 1, 0))  AS 'Jun',
      SUM(IF(MONTH(SessionDate) =  7, 1, 0))  AS 'Jul',
      SUM(IF(MONTH(SessionDate) =  8, 1, 0))  AS 'Aug',
      SUM(IF(MONTH(SessionDate) =  9, 1, 0))  AS 'Sep',
      SUM(IF(MONTH(SessionDate) = 10, 1, 0))  AS 'Oct',
      SUM(IF(MONTH(SessionDate) = 11, 1, 0))  AS 'Nov',
      SUM(IF(MONTH(SessionDate) = 12, 1, 0))  AS 'Dec'
    FROM sessions
    WHERE
    `SessionDate` >= NOW() - INTERVAL 12 MONTH
    GROUP BY CalPos
    ORDER BY CalPos
    
    ) AS b
      ON a.m = b.CalPos
user_171082200
Related