Adding column contents in a SQL UNION query

Question

So far I have this query

SELECT
    COUNT(f.code_id) as item_count, 
    f.code_desc
FROM 
    foo f
    INNER JOIN foohistory fh ON f.history_id = fh.history_id
WHERE
    MONTH(fh.create_dt) = 6
    AND YEAR(fh.create_dr) = 2010
GROUP BY 
    f.code_desc

    UNION ALL

SELECT
    COUNT(b.code_id) as item_count, 
    b.code_desc
FROM 
    bar b
    INNER JOIN barhistory bh ON b.history_id = bh.history_id
WHERE
    MONTH(bh.create_dt) = 6
    AND YEAR(bh.create_dr) = 2010
GROUP BY 
    b.code_desc

My goal is to UNION these two queries add SUM the 'item_count' columns foreach code_desc. Is this possible?

OMG Ponies · Accepted Answer · 2010-10-28 19:09:33Z

5

Without more information about the codes, like if it's possible that codes are mutually exclusive between the two tables, use:

SELECT x.code_desc,
       SUM(x.item_count)
 FROM (SELECT f.code_desc,
              COUNT(f.code_id) as item_count
         FROM foo f
         JOIN foohistory fh ON f.history_id = fh.history_id
        WHERE MONTH(fh.create_dt) = 6
          AND YEAR(fh.create_dr) = 2010
     GROUP BY f.code_desc
       UNION ALL
       SELECT b.code_desc,
              COUNT(b.code_id) as item_count    
         FROM bar b
         JOIN barhistory bh ON b.history_id = bh.history_id
        WHERE MONTH(bh.create_dt) = 6
          AND YEAR(bh.create_dr) = 2010
     GROUP BY b.code_desc) x
GROUP BY x.code_desc

answered Oct 28, 2010 at 19:09

OMG Ponies

334k85 gold badges536 silver badges509 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brumScouse · Accepted Answer · 2010-10-28 19:07:06Z

0

Yeah, doing something like this

SELECT Sum(unionedTable.item_count)
FROM 
(
//your query 


) as unionedTable

answered Oct 28, 2010 at 19:07

brumScouse

3,2461 gold badge28 silver badges39 bronze badges

1 Comment

brumScouse Over a year ago

damn forgot the second roll-up!!

Collectives™ on Stack Overflow

Adding column contents in a SQL UNION query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related