Query to select all rows containing a value that appears less than 8 times

Question

I'm trying to set up a system that has a parent and 8 children. I've done this by having a table of user_id and tier_id. Now I need to get a list of all the tier_id's that appear less than 8 times, so I know that tier is incomplete and then I can go to another list and get replacements.

SELECT * FROM tiers WHERE COUNT(SELECT tier_id) < 8;

I know this isn't right, but maybe it can help explain to a genius what I'm trying to do.

Answer 1

First, your query needs a group by clause, with which the select clause should be consistent:

select tier_id, count(*) cnt
from tiers
group by tier_id

This gives you one row per tier_id, with the count of corresponding rows. Then, you can use a having clause to filter on the count:

select tier_id, count(*) cnt
from tiers
group by tier_id
having count(*) < 8