Counts and total count in one query

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?


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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 first published in February 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top