Challenge:
Let me assume this: we have two tables, one is Regions table with all region information (East, West, etc. ) and we have another table with Sales information. Now, we need to display a report with region information and how many sales conducted in each region with greater than $1000 value in each sale. It sounds simple at the beginning since a Count(*) with Group By can do this trick.
But what if I need to still show the region which does not have any sales greater than $1000 on the report? Using Count(*) itself in the query, the region without any sales greater than $1000 will not show in the query result, period.
I tried to use some Count() tricks (like Count(NULL), COUNT(1), COUNT(0)) to accomplish this task, but found myself no luck at all. Till I decide to use a more complex SQL query to do a Outer Join with the Count() result query.
Here is what I worked out.
Solutions:
To help you understand my processes, let me draw these two tables with some dumb data.
The first table is the Region table as the below.
| Region_ID | Region_Name |
| 1 | East |
| 2 | West |
| 3 | North |
| 4 | South |
Then we have the Sales table now
| Sales_ID | City | Region_id | Amount |
| 1 | Atlanta | 1 | 5000 |
| 2 | Miami | 4 | 200 |
| 3 | New York | 3 | 2000 |
| 4 | Los Angeles | 2 | 1500 |
| 5 | San Francisco | 2 | 700 |
Now, if we just use a simple Count() query, we will have the following result:
SELECT Region, Count(Amount) AS Total
From Sales
Where Amount > 1000
Group By Region_ID
| Region_ID | Total |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
As you can tell from the above, the Region ID 4 – South did not show in the result since its sales amount is only $200. But I need to show this region too with 0 as the sales total.
What I need to take care is two details.
- First, still use the above sql query as SubQuery to create the Count() sales total table (or view), then use the Region table to Outer Join total table to show region even if its sales amount did not meet the query conditions. In our example, I will use Left Join to do such thing.
- Use logic function to show 0 if the value is Null from the outer join tables. In Oracle, it should be NVL, and in Microsoft Access, it will be NZ. For other databases, you can refer to their own manual for the references.
Finally, I will make the query above to the following considering the mentioned two details. This time, I also use Region Name for a good display.
SELECT Region.Region_Name, NVL(SubTotal.Total,0) As SalesTotal
From Region
Left Join
(SELECT Region_ID, Count(Sales.Amount) As Total
From Sales
Where Amount > 1000
Group By Region_ID) SubTotal
On Region.Region_id = SubTotal.Region_ID
With the SQL query above, we have the following result:
| Region_Name | SalesTotal |
| East | 1 |
| West | 1 |
| North | 1 |
| South | 0 |
Have fun on SQLing.
Brilliant – exactly what I needed.
[Reply]
Man, you saved my life
. Thanks a lot!
[Reply]
Great! Thank you!
[Reply]
Great work, thanks a lot!
[Reply]
This is really Brilliant
This is exactly what I needed.
I just did one change in it for SQL Server 2005. I replace NVL with IsNull because SQL Server said NVL is not recognized function
I found this after wasting 3 hours but It really saved my time.
[Reply]
I am confused – in the Query you are using both the table name and the field name Region and it is hard to tell which one you are pointing at (for us dummies who don’t use SQL Query and are attempting to do a more complex query).
[Reply]
hi
my sql server is 2000.
if can`t found nvl but i can found colesce.
i used it but didn`t work.
please help me….
[Reply]
Can one self-join on ONE table to get zeros for missing data?
Let’s say I have a table with:
region
month
dept_code
dept_sales
I want to show every region & dept for a month (CSV example):
August,Northwest,Apparel,4500,Linens,2300,Other,0,SouthEast,Apparel,3900,Linens,1800,Other,2700
September,Northwest,Apparel,0,Linens,2000,Other,0,Southeast,Apparel,4100,Linens,1900,Other,0
[Reply]