Aggregate functions in SQL Server are usually fairly straightforward tools. We normally use them in a GROUP BY clause to present meaningful aggregate information about our data.
I recently came across an interesting problem at work dealing with aggregate functions that I thought would make a great tutorial. I hope you find this tutorial helpful!
Aggregate functions against an empty result set…
Let’s set up some quick data. Take a look at the following Products table:
Here are the CREATE TABLE and INSERT statements if you want to create the data in your own environment and follow along:
CREATE TABLE Products(
ProductID int IDENTITY(20,2) NOT NULL,
ProductName varchar(20) NULL,
Price decimal(5, 2) NULL
)
INSERT Products (ProductName, Price)
VALUES
('Large Bench', 198.00),
('Small Bench', 169.40),
('Coffee Table', 220.00),
('Side Tables', 265.20),
('Coat Rack', 45.00)
Let’s think about some information we might want to see. Maybe we would like to know how much money we are making from a specific kind of product. For example, we can use wildcards to show us the total selling price of our ‘Bench‘ products:
SELECT SUM(Price) as 'Total selling price for all benches' FROM Products WHERE ProductName like '%Bench%'
Here’s the result:
Great, that checks out. It looks like we are selling our ‘Bench‘ products for a total of $367.40. If we wanted to see the income for a different set of products, all we need to do is change what we’re searching for in the WHERE clause.
Pop Quiz: What would be returned by the SUM aggregate if our WHERE clause excludes all rows? That is, we reference a product name that does not exist?
Here’s an example:
What would be the result of this query?
Aggregate functions return NULL if there is nothing to aggregate
The result of our query won’t be 0, nor will it be an empty result set. The result would be NULL:
This can cause problems if you are relying on a number to be returned from this query.
If you need a number to return from your query, you could choose to use the ISNULL function to return 0 instead of NULL:
Nice.
We do get an empty result set if we use the GROUP BY clause with an aggregate function
Real quick, I discovered that this same problem doesn’t seem to happen if we use an aggregate function with a GROUP BY clause.
Take a look at the following Orders table:
Notice the ProdID column. This is basically a foreign link back to the Products table. This is how we know what product was purchased in the order.
If we wanted to know how much income we have made from a single product, we can write a fairly simple JOIN query that uses the GROUP BY clause and the SUM aggregate function:
But in the WHERE clause, if we outline a ProductID that does not exist, the result is an empty result set:
So, the problem of NULL getting returned only seems to be a problem if we don’t use the GROUP BY clause. If anyone has any input about this, I would love to hear it in the comments!
Next Steps:
Leave a comment if you found this tutorial helpful!
If you need a full rundown on aggregate queries and the GROUP BY clause, take a look at the full beginner-friendly tutorial:
GROUP BY clause: A How-To Guide
Also, NULL is an interesting thing in SQL Server. You can learn all about what it is and how it works by checking out the full tutorial:
SQL Server NULL: Are you making these 7 mistakes?
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
