Grouping by column and rows

I have a table like this:

+----+--------------+--------+----------+
| id |     name     | weight | some_key |
+----+--------------+--------+----------+
|  1 | strawberries |     12 |        1 |
|  2 | blueberries  |      7 |        1 |
|  3 | elderberries |      0 |        1 |
|  4 | cranberries  |      8 |        2 |
|  5 | raspberries  |     18 |        2 |
+----+--------------+--------+----------+

I’m looking for a generic request that would get me all berries where there are three entries with the same ‘some_key’ and one of the entries (within those three entries belonging to the same some_key) has the weight = 0

in case of the sample table, expected output would be:

1 strawberries
2 blueberries
3 cranberries

Answer

Try this-

SELECT some_key,
SUM(weight) --Sample aggregations on column 
FROM your_table
GROUP BY some_key
HAVING COUNT(*) = 3 -- If you wants at least 3 then use >=3
AND SUM(CASE WHEN weight = 0 THEN 1 ELSE 0 END) >= 1

As per your edited question, you can try this below-

SELECT id, name
FROM your_table
WHERE some_key IN (
    SELECT some_key
    FROM your_table
    GROUP BY some_key
    HAVING COUNT(*) = 3 -- If you wants at least 3 then use >=3
    AND SUM(CASE WHEN weight = 0 THEN 1 ELSE 0 END) >= 1
)