Understanding SQL COUNT and Other Aggregate Function

We have seen how to use the SQL Count function in a GROUP BY query, but did you know there are several other functions you can use there as well? In this tutorial, let’s look at several other SQL aggregate functions, and how they can be used to summarize your data.

For these examples, we’ll use the CustomerOrderDetail view from the PizzaDB

Suppose you wanted to know the purchasing habits of each customer. Let’s put together a query to summarize PurchaseAmount. We’ll look at how we can use MIN, MAX, COUNT, SUM, and AVG to create an overall spending picture.

/* Answer */
select CustomerName,
    min(PurchaseAmount) MinimumPurchased,
    max(PurchaseAmount) MaximumPurchased,
    count(PurchaseAmount) NumberOfPurchases,
    sum(PurchaseAmount) TotalPurchases,
    avg(PurchaseAmount) AveragePurchase
from CustomerOrderDetail
group by CustomerName
order by CustomerName

In this example we used the most common SQL aggregate functions to calculate summaries such as COUNT and SUM. Listed below are the functions used.

Write a query to return the number pizza’s ordered each day. Include the total amount of pizzas purchased in your SQL along with a count.

/* Answer */
select OrderDate,
    count(PurchaseAmount) NumberOfPurchases,
    sum(PurchaseAmount) TotalPurchases
from CustomerOrderDetail
where ProductName like '%Pizza%'
group by OrderDate
order by OrderDate

To write this query I used the SQL COUNT and SUM functions and grouped by OrderDate. Also, one that is easy to forget, but you need to include is a WHERE clause. Since we are only looking to tally up quantities for Pizza, don’t forget to add a condition to only find ProductName like Pizza.

You can also use aggregate function without a GROUP BY. When doing so, they operation on the entire result. For example here is an example of how to get a count of all pizza orders.

/* Answer */
select count(PurchaseAmount) NumberOfPurchases
from CustomerOrderDetail
where ProductName like '%Pizza%'

Keep in mind that when using an aggregate function to count all rows, you can only include the functions. If you include columns the result fails. Why? Because if you include columns, you need to also include a GROUP By clause!