Mysql – How to count specific products

MySQL

I have a table product and its columns are maker, type and model. I want to get the makers who produce 6 product types and more than one model. Here is something I am trying:

select maker, type 
from product 
group by maker
having count(distinct type) = 6 
   and count(distinct model) > 1 ;

It gives error.

If I say I need 1 type and query is:

select maker, min(type) 
from product 
group by maker
having count(distinct type) = 1 
   and count(distinct model) > 1 ;

then it works. Why?

Best Answer

In your first query the error is produced by the attribute type, which is in the select clause, but not in the group by clause. This is due to the fact that there are different values of type for each group. So, instead of writing:

select maker, type from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1

you should either remove type:

select maker from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1

or use an aggregation function for it, like in the second query:

select maker, min(type) from product group by maker
having count(distinct type) = 6 and count(distinct model) > 1
Related Question