CODEWITHSUNDEEP
mysql
digestivee
digestivee

Reputation: 740

Find the value of one column based on maximum value in another column per group

I have a query which gives me three columns: an ID, the day of week of event and count of how many events each day of week has, i.e.

ID    Day_Name    Cnt
1     Thursday    1
2     Monday      3
2     Thursday    2
2     Sunday      2
3     Tuesday     7
3     Wednesday   3

I get this by using query

SELECT P.ID, DAYNAME(E.EVENT_DATE) AS Day_Name, COUNT(*) AS Cnt
FROM EVENT AS E

INNER JOIN PERSON AS P
ON P.ID_2 = E.ID_2

WHERE E.EVENT_DATE > '2016-01-01'
AND E.EVENT_STATUS LIKE '%OCCURED%'

GROUP BY P.ID, DAYNAME(E.EVENT_DATE)

I would like to reduce this query to only return the day of week for each user with the maximum count. At the same time, I would like to change the column with counts to instead show the frequency of events for that weekday. For the example above I would like to change the output to be

ID    Day_Name    Frequency
1     Thursday    1
2     Monday      0.429
3     Tuesday     0.7

Thankful if anyone got an idea

Upvotes: 2

Views: 66

Answers (2)

Strawberry
Strawberry

Reputation: 33935

So here's a partial answer...

SELECT a.*
  FROM ([your query here]) a
  JOIN 
     ( SELECT id,MAX(cnt) cnt FROM ([your query here]) GROUP BY id ) b
    ON b.id = a.id
   AND b.cnt = a.cnt;

For a more complete answer I suggest you provide the original 18 row data set. See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

To get the expected result set you could do your calculation in outer query like

SELECT ID,
SUBSTRING_INDEX(GROUP_CONCAT(Day_Name ORDER BY Cnt DESC),',',1) Day_Name,
MAX(Cnt)/SUM(Cnt) Frequency
FROM(
    SELECT P.ID, DAYNAME(E.EVENT_DATE) AS Day_Name, COUNT(*) AS Cnt
    FROM EVENT AS E
    INNER JOIN PERSON AS P
    ON P.ID_2 = E.ID_2
    WHERE E.EVENT_DATE > '2016-01-01'
    AND E.EVENT_STATUS LIKE '%OCCURED%'
    GROUP BY P.ID, DAYNAME(E.EVENT_DATE)
) t
GROUP BY ID

Upvotes: 1

Related Questions