How to display
0 in conditional [group by] report in Count() SQL Query
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_ID, 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 Jointotal
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, in Microsoft SQL it is IsNull, 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
Great! We had
it! Of course, my initial challenge was NOT such dumb Sales and
Region tables. I just used them for the simplicity purpose on my
blog. But we still can get it with the SubQuery strategy above. So
even you have more complex reports requirement, you still can use
the above as your starting point.