CODEWITHSUNDEEP
mysqlsqlselectcount
user1260928
user1260928

Reputation: 3439

MySql : issue with GROUP BY and COUNT clauses

I have this table

enter image description here

I would like to display products(with their type) that have at least two different types.

Here :

enter image description here

I have tried this :

SELECT PRODUCT, TYPE FROM MYTABLE
group by PRODUCT, TYPE
HAVING count(PRODUCT) > 1;

but it doesn't give me the result I want.

Upvotes: 1

Views: 42

Answers (1)

Mureinik
Mureinik

Reputation: 312257

The problem here is you want to count by a different grouping than you want to display. One way around this is two have the counting in a subquery:

SELECT DISTINCT product, type
FROM   mytable
WHERE  product IN (SELECT   product
                   FROM     mytable
                   GROUP BY product
                   HAVING   COUNT(DISTINCT type) > 1)

Upvotes: 4

Related Questions