How Does Oracle SQL display queries with group by aggregation result count 0?

Source: Internet
Author: User
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 to sell conducted in each region with greater than $1000 value in each sale. it sounds simple at the beginning sinceCount (*)WithGroupCan 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 isRegionTable as the below.

Region_id Region_name
1 East
2 West
3 North
4 South

Then we haveSalesTable 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,Region ID 4-SouthDid not show in the result since its sales amount is only $200. But I need to show this region too0As the sales total.

What I need to take care is two details.

  1. 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 useLeft joinTo do such thing.
  2. Use logic function to show 0 if the value is nullFrom the Outer Join tables.In Oracle, it shoshould 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 abve. so even you have more complex reports requirement, you still can use the above as your starting point.

From http://lichao.net/eblog/how-to-display-0-in-conditional-group-by-report-in-count-sql-query-200808164.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.