Q

Totals from a UNION query

I'm trying to retrieve a count of rows using a union, but for some reason I get two rows returned with different counts.

I'm trying to retrieve a count of rows using a union, but for some reason I get two rows returned with different

counts. I'm assuming one count is from one table, and the second is from the other.

select category2, category3, category4, 
       count(*) as my_count
from table1
group by category2, category3, category4
UNION ALL
select category2, category3, category4, 
       count(*) as my_count
from table2
group by category2, category3, category4

How do I merge these counts???

There are two ways.

select category2, category3, category4
     , count(*) as overall_count
  from (
       select category2, category3, category4
         from table1
       UNION ALL
       select category2, category3, category4
         from table2
       ) as d
group by category2, category3, category4

The first way, all the detail rows are combined by the UNION subquery, and then the outer query just takes totals.

select category2, category3, category4
     , sum(subtotal) as overall_count
  from (
       select category2, category3, category4
            , count(*) as subtotal
         from table1
       group by category2, category3, category4
       UNION ALL
       select category2, category3, category4
            , count(*) as subtotal
         from table2
       group by category2, category3, category4
       ) as d
group by category2, category3, category4

The second way, subtotals are prepared for each table, then summed.

Notice that the first query has only one GROUP BY, whereas the second has three. Which of these do you think will be more efficient? Which SQL will be easier to maintain?

This was first published in March 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close