CODEWITHSUNDEEP
mysqlsql
Juraj Paulik
Juraj Paulik

Reputation: 45

MySQL MAX from count query

I have table rozpis_riesitelov which contains columns :
id_rozpisu_riesit, id_zam, id_projektu, id_ulohy.

I made query : 

select id_zam, id_ulohy, count(*) as counted 
from rozpis_riesitelov 
group by id_zam 
having id_ulohy in (1,2,8)

which shows me id of employee (id_zam) and how many times He was in project (id_ulohy is irrevelant but I had to select it beacuse of having clause). It shows me everyone in db but I am looking for employee with ID of 4 who is in 6 projects (Yes, I could do order by but I want to see max). When I do max of this query like this:

select max(counted) 
from (select id_zam, id_ulohy, count(id_zam) as counted 
      from rozpis_riesitelov 
      group by id_zam 
      having id_ulohy in (1,2,8)) as riesitel

which shows me number 149 instead of 6.

So basically I only need to find employee that occurs in the most of the projects.

Upvotes: 0

Views: 63

Answers (2)

Leslie
Leslie

Reputation: 3644

Not sure exactly what you are trying to accomplish but you only use HAVING to filter on your aggregate like this:

HAVING COUNT(*) > 1

you should be able to move the condition to a WHERE clause and get correct max returned:

select max(counted) 
from (select id_zam, count(id_zam) as counted 
      from rozpis_riesitelov       
      where id_ulohy in (1,2,8)
group by id_zam) as riesitel

Upvotes: 0

BenM
BenM

Reputation: 53198

What's wrong with sorting by the COUNT() value, and limiting to one result?

SELECT `id_zam`, 
       `id_ulohy`, 
       COUNT(*) AS `counted` 
FROM   `rozpis_riesitelov ` 
WHERE  `id_ulohy` IN ( 1, 2, 8 ) 
GROUP  BY `id_zam` 
ORDER  BY `counted` DESC 
LIMIT  1

Upvotes: 1

Related Questions