The Where clause is great. It helps you filter out items from a table, leaving only the records you want. But sometimes you are not looking for the actual records. Instead, sometimes you only want to know how many records.
In this example, I will using the [HumanResources].[EmployeeDepartmentHistory] from AdventureWorks2012:
Count and Group By
The Count(*) function is used to count the number of rows.
Now, when we combine count(*) with a Group By clause, we can count how many records exist for each element in our group by clause.
All non aggregate columns in the select statement need to be in the group by function or you will get an error.
The code below counts the amount of rows containing each departmentID
Having
Run the following query:
select BusinessEntityID, count(*) as amt from [HumanResources].[EmployeeDepartmentHistory] group by BusinessEntityID
You will get a list 290 records long. Most of these records will have a 1 in the amt column (meaning there is only one records containing the BusinessEntityID referenced)
If you want to know how many BusinessEntityIDs in this table have more than one record, we just have to add the Having clause to our query.
having can only be used in conjunction with aggregate functions.
Min(), Max()
Min and Max return the minimum and maximum of whatever column you you place in the parenthesis
