Q
Problem solve Get help with specific problems with your technologies, process and projects.

Counts and total count in one query

I have two queries:

select place, count(*) 
  from tableA group by place

  
Place A    2
Place B    3
Place C    5

  
select count(place) 
  from tableA

  
10

These return a list of places and their count, and a total count for all places. Is there a way to do this in one statement?


 

Yes. Combine them with UNION ALL. Note that your first query returns two columns, place and count, whereas your second returns only one, the count. Therefore we need to use a "placeholder" value in the second query. This is because each query in the union must return the same number of columns. But that's okay, because the placeholder we will use is the literal ' Total' which will help identify what the count is.

select place
     , count(*)  as places
  from tableA
group
    by place
union all
select '    Total'
     , count(*)
  from tableA 
order
    by case when place='    Total'
            then 1 else 0
        end
     , place 

  


Place A      2
Place B      3
Place C      5
    Total   10

You can clearly see what each of the two queries is doing. A simple UNION query. Somewhat bulky, but straightforward. Okay, except maybe for the ORDER BY.

In a union, the ORDER BY might look like it's part of the last query of the union, but it actually operates on the entire combined result sets of all queries in the union. So what does the CASE expression do in the ORDER BY above? It ensures that the "Total" line in the result set sorts last.

Check to see whether your database supports the WITH ROLLUP option, as Microsoft SQL Server does:

select place
     , count(*)  as places
  from tableA
group
    by place
  with rollup

  


Place A      2
Place B      3
Place C      5
NULL        10

Lovely syntax, very intuitive. To make the NULL say 'Total' requires a small adjustment:

select coalesce(place,'    Total') as theplace
     , count(*)  as places
  from tableA
group
    by place
  with rollup
order
    by case when place is null
            then 1 else 0
        end
     , place 

  


Place A      2
Place B      3
Place C      5
    Total   10

The COALESCE catches the NULL produced by the ROLLUP.

This was last published in February 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close