CODEWITHSUNDEEP
sqlt-sql
user9267811
user9267811

Reputation: 27

SQL count with having

CustomerID, is unique.

What result is given with this question?

SELECT personalnumber, 
   Amount = Count(CustomerID), 
FROM   Customer
GROUP  BY personalnumber 
HAVING Count(CustomerID) > 100

What does it mean that having count(CustomerID) > 100?? Is it that the CustomerID has to be above 100?

Upvotes: 1

Views: 104

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

You have been given two answers, but now you've edited your request without accepting one of them. So I guess you haven't understood the answers given so far.

GROUP BY personalnumber

means that you want one result row per personalnumber. All records for one personalnumber get aggregated to a single row.

Count(CustomerID)

counts all occurrences where CustomerID is not null.

HAVING Count(CustomerID) > 100

limits your result to those personalnumber that have more than 100 records with a CustomerID.

As this is the Customer table, we must assume that CustomerID is the table's ID, uniquely identifying a record, and it can't be null.

So this should better be written as 

HAVING Count(*) > 100

meaning just the same: limit the result to those personalnumber that occur in more than 100 records. (Count(*) means: count rows.)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

It means that the personalnumbers returned by the query are on more than 100 rows where CustomerId is not null.

If the code just wanted to count rows, I would recommend:

having count(*) > 100

This doesn't depend on the nullability of a column.

If customerid could be duplicated and you wanted to check for 100 different values, then you would use:

having count(distinct CustomerId) > 100

If you were filtering on CustomerId, you would use a where clause before the group by.

Upvotes: 1

Nick
Nick

Reputation: 147146

It means that the personalnumber has more than 100 associated CustomerID's

Upvotes: 1

Related Questions