oracle中结果为0时,oracle SQL 如何显示group by 聚合结果count 为0

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.