CODEWITHSUNDEEP
sqlcount
ricksmt
ricksmt

Reputation: 899

COUNT(column) returns COUNT(*)

I using this website to practice SQL. I've got this query:

SELECT DISTINCT maker
FROM Product
GROUP BY maker
HAVING COUNT(type) = 1
AND COUNT(model) > 1

For some reason both count aggregates return the same value--as if they were COUNT(*)--but this isn't what I'm expecting. Please explain why and, if it's not too much trouble, what the correct approach is.

Upvotes: 0

Views: 106

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your having clause is:

HAVING COUNT(type) = 1 AND COUNT(model) > 1

Each component is counting the number of non-NULL rows with a value in that column. So, if type contained 200 NULLs and 100 '1's, the value would be 100. Count(*), in this case, would return the number of rows, or 300.

Perhaps you want to count the number of distinct values in each column. In that case, you can use:

HAVING COUNT(DISTINCT type) = 1 AND COUNT(DISTINCT model) > 1

In practice, though, COUNT(DISTINCT) usually uses more resources than other aggregation functions. The following does the same thing and often performs better:

HAVING min(type) = max(type) and min(model) < max(model)

Upvotes: 1

Seyed Hamed Shams
Seyed Hamed Shams

Reputation: 615

Count() aggregate function, counts the number of records of the table you are query. (Product Table) There is no difference that which column you give it as input. It will return the same output as you said. And it's completely normal.

Upvotes: 0

Related Questions