CODEWITHSUNDEEP
sqlpostgresqlcountgroup-by
Athanasia Pavlidou
Athanasia Pavlidou

Reputation: 199

Finding the highest COUNT in a GROUP BY query

Hello I am new in postgresql . Here is my code for grouping the options and counting them . I want a way though to get only the option with the max count . Is there any way ?

SELECT s.option, COUNT(*)
FROM "Set" as s, "Lecture" as l, "Process" as p, "donation" as d
WHERE d.code = p.code and l.type = p.type and l.option = s.option
GROUP BY s.option

Upvotes: 2

Views: 4752

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

You can simplify the query. Not only by using PROPER, explicit JOIN syntax, but also by getting rid of Set:

SELECT l.option, COUNT(*)
FROM "Lecture" l JOIN
     "Process" p
     ON l.type = p.type JOIN
     "donation" d
     ON d.code = p.code
GROUP BY l.option
ORDER BY COUNT(*) DESC
FETCH FIRST 1 ROW ONLY;

Upvotes: 0

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477794

Simply give the child a name (for instance count), ORDER BY that name, and LIMIT to 1:

SELECT s.option, COUNT(*) AS count -- give the child a name
FROM "Set" as s, "Lecture" as l, "Process" as p, "donation" as d
WHERE d.code = p.code and l.type = p.type and l.option = s.option
GROUP BY s.option
ORDER BY count DESC -- order by that child in descending order
LIMIT 1 -- and limit the output to one row

If you want the highest, you should add DESC to the ORDER BY. In case you want the lowest; either do not specify or add ASC.

Upvotes: 2

Related Questions