SQL group by and having clause
The clause “Group By” is very similar to the english word “group” or “classify”. In our daily life we classify anything and everything. It helps us count a set of objects easily. In database, we use “Group By” to perform calculation on a set of rows which have some common value or can be grouped together. In Customers table (as mentioned in previous articles), we can group or classify Customers by State. So if you want to find out the total number customers by state you will use the Group By clause. Along with Group By clause you will need to use an aggregate function such as count/sum function in the Select clause.
Select state, count(*) as TotalCustomers
from Customers
group by state
You can also write count(CustomerID) instead Count(*) to get the total customer count by state.
Count(*) adds the numbers of rows that exists per state in the Customers Table.
However, count(CustomerID) counts the records that have a CustomerID. In the above case, since each record have a CustomerID, we will get the same result.
You will get different results for Count(CustomerID) if CustomerID was nullable i.e. may or may not hold a value. You can ignore this scenario for the time being. This is not a common scenario but we do come across cases like this, especially when the data is not clean for some reason, i.e. information not collected properly or lost during migration.
You will also get inaccurate customer count for count(CustomerID) if CustomerID value was not unique i.e. have same CustomerID value in more than one rows/records. This is a scenario which you will come across regularly, infact is applicable to our Orders table. I have added a few more records to the orders table to explain this and this is what we have now in the table.
Suppose you want to find out how many customers have bought product A(ProductID=1). We see that CustomerID {3,6,7,12} have bought ProductID=1. Although, CustomerID {3} has bought the product twice, only 4 customers(not 5) that have bought ProductID=1.
When we run the query without and with distinct, we get 5 and 4 respectively and obviously the later returns the accurate count.
Try this query with any column in any tables that have duplicate values and you will see the difference in count. Along with count, there are a few more aggregate functions available that are very useful. Lets look at some scenarios when you can use them. I have added a new column “Price” in Products table and set a price for each of the 4 products. To see how much the customers have spent in their individual orders, we write the following query.
Aggregate Functions
SUM: Sum returns you summation(obviously) of numeric data of a column grouped by data from another non-numeric column like FirstName, LastName.
To see, how much each customer has spent in all, we will use the aggregate function SUM around Quantity * Price and group it by a.FirstName +' '+a.LastName.
MAX: If you Max instead of Sum, you get to see the maximum amount each customer have spent.
MIN: You can also use these aggregate function without the group by clause. In that case, it will look at all rows and return min/max values. For example, to find what is the minimum any customer have spent in any order, you can use min clause around Quantity * Price and remove the group by clause like this.
