Menu
How to create group if at least one value in group meets condition?
Here is an example of DB table test:
| ID | TYPE | COLOR |
|====|======|=======|
| 1 | 1 | R |
| 2 | 1 | B |
| 3 | 1 | G |
| 4 | 2 | B |
| 5 | 2 | G |
| 6 | 3 | G |
I need to select all TYPE values that have more than one row, and at least one of COLORs is G for that TYPE.
So pseudo-select would look like this:
select TYPE
from test
group by TYPE
having count(*) > 1
and count(COLOR = 'G') > 0
918
Syntax: SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2; function_name: Name of the function used for example, SUM() , AVG(). table_name: Name of the table. condition: Condition used.
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
Because the COALESCE is an expression, you can use it in any clause that accepts an expression such as SELECT , WHERE , GROUP BY , and HAVING .
Group by is one of the most frequently used SQL clauses. It allows you to collapse a field into its distinct values. This clause is most often used with aggregations to show one value per grouped field or combination of fields.
With the OP's modified requirement:
select type
from test
group by type
having count(*) > 1 and count(case when color = 'G' then 0 end) > 0
;
count only counts non-null values. A neat trick to wrap both required conditions into one is to count the distinct number of a case expression that returns something for G and something else for any other value:
SELECT type
FROM test
GROUP BY type
HAVING COUNT(DISTINCT CASE color WHEN 'G' THEN 1 ELSE 2 END) = 2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
