Find maximum number of occurrences for a given weekday

Question

I have a table containing test data with 2 columns I'm working with : "date" and "quadrant". The quadrant column is an enum value with 4 possible values (1,2,3 or 4).

Each day of the week (with 4 months worth of date) has an enum value associated to it.

The enum values regularly appears several times for any given day.

I am trying to work out the most common occurrence of the quadrant enum value for a given weekday throughout the entire table.

For example, Monday has seen 20 enum values of "1" and 19 values of "2".

I'd like to just see the most common, which in this example is "1".

Something like this:

Monday.........1
Tuesday........3
Wednesday......4
Thursday.......1
Friday..........2

So far i have tried the following but it only gives me the total number of times the quadrant column has an enum value for each weekday.

SELECT DAYNAME(Date) AS Weekday
     , COUNT(quadrant) AS 'Most Likey Damaged Quadrant' 
  FROM damage_tbl 
 WHERE quadrant IS NOT NULL 
 GROUP 
    BY quadrant 
 ORDER
    BY WEEKDAY

Here is a link to the table and all the data on rextester: https://rextester.com/UQDSB56921

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Oct 18 '18 at 18:06
This is quite similar to what you need: https://stackoverflow.com/questions/4556653/is-there-something-equivalent-to-argmax-in-sql — Yossi Vainshtein, Oct 18 '18 at 18:27

score 0 · Answer 1 · answered Oct 18 '18 at 18:43

You can check this post to see the different ways of accomplishing this (getting argmax in MySQL)

One of the ways (mentioned there in a comment) is Self-anti join:

SELECT a.Weekday, a.quadrant FROM (
    SELECT DAYNAME(Date) AS Weekday, quadrant, COUNT(*) AS 'cnt' 
      FROM damage_tbl 
     WHERE quadrant IS NOT NULL 
     GROUP 
        BY WEEKDAY, quadrant
     ORDER
        BY WEEKDAY, count(*) desc ) a LEFT JOIN (
    SELECT DAYNAME(Date) AS Weekday, quadrant, COUNT(*) AS 'cnt' 
      FROM damage_tbl 
     WHERE quadrant IS NOT NULL 
     GROUP 
        BY WEEKDAY, quadrant
     ORDER
        BY WEEKDAY, count(*) desc ) b 
ON a.Weekday = b.Weekday and a.cnt < b.cnt  
WHERE b.Weekday is null

Mate, this is spot on. Thank you very much. It helped me learn to do many other things. — R T, Oct 29 '18 at 20:40
No problem. If you found the answer helpful, you can upvote it and\or mark as correct. — Yossi Vainshtein, Oct 30 '18 at 17:39

Find maximum number of occurrences for a given weekday

1 Answers1