CODEWITHSUNDEEP
mysqlsql
András Iványi
András Iványi

Reputation: 317

MySQL selective GROUP BY, using the maximal value

I have the following (simplified) three tables:

user_reservations:

id | user_id |
 1 | 3       |
 1 | 3       |

user_kar:

id | user_id | szak_id |
 1 | 3       | 1       |
 2 | 3       | 2       |

szak:

id | name |
 1 | A    |
 2 | B    |

Now I would like to count the reservations of the user by the 'szak' name, but I want to have every user counted only for one szak. In this case, user_id has 2 'szak', and if I write a query something like:

SELECT sz.name, COUNT(*) FROM user_reservations r 
   LEFT JOIN user_kar k ON k.user_id = r.user_id
   LEFT JOIN szak s ON r.szak_id = r.id

It will return two rows:

A | 2 |
B | 2 |

However I want to every reservation counted to only one szak (lets say the highest id only). I tried MAX(k.id) with HAVING, but seems uneffective.

I would like to know if there is a supported method for that in MySQL, or should I first pick all the user ID-s on the backend site first, check their maximum kar.user_id, and then count only with those, removing them from the id list, when the given szak is counted, and then build the data back together on the backend side?

Thanks for the help - I was googling around for like 2 hours, but so far, I found no solution, so maybe you could help me.

Upvotes: 0

Views: 197

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

Something like this?

SELECT sz.name, 
       Count(*) 
FROM   (SELECT r.user_id, 
               Ifnull(Max(k.szak_id), -1) AS max_szak_id 
        FROM   user_reservations r 
               LEFT OUTER JOIN user_kar k 
                            ON k.user_id = r.user_id 
        GROUP  BY r.user_id) t 
       LEFT OUTER JOIN szak sz 
                    ON sz.id = t.max_szak_id 
GROUP  BY sz.name;

Upvotes: 1

Related Questions