Return all values with same count

Question

I have following query

select id, nameOfPet, count(fed)
from petLover, pets
where id = 180 and petLover.nameOfPet = pets.nameOfPet
group by fed
order by fed desc;

So what the query does is get a person's id, get all the names of the pets he has, looks in the table pets for the same person and checks which pet has been fed how many times and output the person's id, the name of the pet and how often it has been fed.

Now I want to only output the pet that has been fed the most. I could certainly use limit 1, but I want to output all, if the number of feeding is the same for several pets.

What determines the number of times a pet has been fed? Is it a column? Or is it how many rows exist in the pets table? Can you post your table schema? — Zane Bien, Jul 08 '12 at 09:49
I presume that maintains a number of times the pet has been fed? If so, it looks like you might need to change your COUNT() aggregates to SUM() aggregates. — Spikeh, Jul 08 '12 at 10:12
is the nameOfPet textual? you don't normally join on name/description. You join on CODE or ID column. Kindly post your tables DDL, so stackoverfellows can correctly answer your query, can avoid guessworks — Michael Buen, Jul 08 '12 at 12:35

score 2 · Accepted Answer · answered Jul 08 '12 at 09:55

2

The nested query derived the counts. Other than having only a single column it is identical to the outermost query.

select id, nameOfPet, count(fed)
from petLover, pets
where id = 180 and petLover.nameOfPet = pets.nameOfPet
group by fed
having count(fed) >= ALL (
    select count(fed)
    from petLover, pets
    where id = 180 and petLover.nameOfPet = pets.nameOfPet
    group by fed
)

answered Jul 08 '12 at 09:55

shawnt00

16,443
3
17
22

This recent question is basically the same: http://stackoverflow.com/questions/11381674/sql-selecting-all-rows-with-maximum-value/11381856#11381856 – shawnt00 Jul 08 '12 at 09:56

Spikeh · Answer 2 · 2012-07-08T10:14:41.810

The query you have posted won't run; you would need to group by id, nameOfPet. This is personal preference, but I would also specify your join (to make it more readable and easier to change between types of join):

SELECT id, nameOfPet, COUNT(p.fed)
FROM petLover pl
LEFT OUTER JOIN pets p ON pl.nameOfPet = p.nameOfPet
WHERE id = 180
GROUP BY id, nameOfPet
ORDER BY COUNT(p.fed)

The LEFT OUTER JOIN will make sure you return all of the results from petLover, even if none have been fed (i.e. if none have been fed, you will return all petLovers). Change it back to an INNER JOIN if you only want results when an animal has been fed. Here's a modified query to do what you're looking for (based upon rows):

SELECT pl.id, pl.nameOfPet, COUNT(*)
FROM petLover pl
LEFT OUTER JOIN pets p ON pl.nameOfPet = p.nameOfPet
GROUP BY pl.id, pl.nameOfPet
HAVING COUNT(*) >= ALL (
    SELECT COUNT(*)
    FROM petLover pl
    LEFT OUTER JOIN pets p ON pl.nameOfPet = p.nameOfPet
    GROUP BY pl.id, pl.nameOfPet
)
ORDER BY COUNT(*) DESC

EDIT

Further to the answer to my question in the original comments, you should be able to do the following to modify the SQL above:

SELECT pl.id, pl.nameOfPet, SUM(p.fed)
FROM petLover pl
LEFT OUTER JOIN pets p ON pl.nameOfPet = p.nameOfPet
GROUP BY pl.id, pl.nameOfPet
HAVING SUM(p.fed) >= ALL (
    SELECT SUM(p.fed)
    FROM petLover pl
    LEFT OUTER JOIN pets p ON pl.nameOfPet = p.nameOfPet
    GROUP BY pl.id, pl.nameOfPet
)
ORDER BY SUM(p.fed) DESC

Return all values with same count

2 Answers2