 |
|
|
| > |
QUESTION POSED ON: 08 March 2007
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???
|
|
|
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
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?
|
|
|
|
|
|
|
|
Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and
answer pairs from more than 250 TechTarget industry experts.
|
|
|
|
|
|
|
|
|
|
|
|
