SQL UNION issue when counting records

Question

I have the following database schema (names changed to protect the innocent)

Code Table

| code_id | code_desc |
|---------|-----------|
| 1       | good      |
|---------|-----------|
| 2       | bad       |
|---------|-----------|
| 3       | ugly      |
|---------|-----------|

Foo Table                               AssignedFoo Table

| foo_id | foo_desc | foo_dt    |       | assn_id | foo_id | code_id |
|--------|----------|-----------|       |---------|--------|---------|
| 101    | red      | 1/1/2000  |       | 301     | 101    | 1       |
|--------|----------|-----------|       |---------|--------|---------|
| 102    | green    | 6/1/2000  |       | 302     | 102    | 2       |
|--------|----------|-----------|       |---------|--------|---------|
| 102    | blue     | 12/1/2000 |
|--------|----------|-----------|

Bar Table                               AssignedBar Table

| bar_id | bar_desc | bar_dt    |       | assn_id | foo_id | code_id |
|--------|----------|-----------|       |---------|--------|---------|
| 201    | gold     | 12/1/2000 |       | 401     | 201    | 1       |
|--------|----------|-----------|       |---------|--------|---------|
| 202    | silver   | 1/1/2001  |       | 402     | 202    | 3       |
|--------|----------|-----------|       |---------|--------|---------|
| 202    | bronze   | 6/1/2001  |
|--------|----------|-----------|

I want to count the number of records that are mapped to the Code table regardless if they're foos or bars. I had though that the following query would work.

SELECT
    COUNT(c.code_id) AS coded,
    c.code_desc
FROM 
    code c
    INNER JOIN assignedfoo af ON af.code_id = c.code_id
    INNER JOIN foo f ON f.foo_id = af.foo_id
WHERE
    f.foo_dt >= [start date] AND 
    f.foo_dt <= [end date]
GROUP BY 
    c.code_desc

UNION ALL   

SELECT
    COUNT(c.code_id) AS coded,
    c.code_desc
FROM 
    code c
    INNER JOIN assignedbar ab ON ab.code_id = c.code_id
    INNER JOIN bar b ON b.bar_id = ab.bar_id
WHERE
    b.bar_dt >= [start date] AND 
    b.bar_dt <= [end date]
GROUP BY 
    c.code_desc 

Unfortunately, I get the following result set

| coded | code_desc |
|-------|-----------|
| 1     | good      |
|-------|-----------|
| 1     | good      |
|-------|-----------|
| 1     | bad       |
|-------|-----------|
| 1     | ugly      |
|-------|-----------|

What I want to get is this

| coded | code_desc |
|-------|-----------|
| 2     | good      |
|-------|-----------|
| 1     | bad       |
|-------|-----------|
| 1     | ugly      |
|-------|-----------|

Any suggestions about how to fix this?

Any help would be greatly appreciated.

Thanks!

Answer 1

I think you simply need to move the group by/count outside of your union like this:

select count(a.code_id) as Coded, a.code_desc
from (
SELECT
    c.code_id,
    c.code_desc
FROM 
    code c
    INNER JOIN assignedfoo af ON af.code_id = c.code_id
    INNER JOIN foo f ON f.foo_id = af.foo_id
WHERE
    f.foo_dt >= [start date] AND 
    f.foo_dt <= [end date]

UNION ALL   

SELECT
    c.code_id,
    c.code_desc
FROM 
    code c
    INNER JOIN assignedbar ab ON ab.code_id = c.code_id
    INNER JOIN bar b ON b.bar_id = ab.bar_id
WHERE
    b.bar_dt >= [start date] AND 
    b.bar_dt <= [end date]
) a
group by a.code_desc

Answer 2

use this

Select COUNT(UnionTable.coded),UnionTable,code_description from 

(SELECT
    COUNT(c.code_id) AS coded,
    c.code_desc code_description
FROM 
    code c
    INNER JOIN assignedfoo af ON af.code_id = c.code_id
    INNER JOIN foo f ON f.foo_id = af.foo_id
WHERE
    f.foo_dt >= [start date] AND 
    f.foo_dt <= [end date]
GROUP BY 
    c.code_desc

UNION ALL   

SELECT
    COUNT(c.code_id) AS coded,
    c.code_desc code_description
FROM 
    code c
    INNER JOIN assignedbar ab ON ab.code_id = c.code_id
    INNER JOIN bar b ON b.bar_id = ab.bar_id
WHERE
    b.bar_dt >= [start date] AND 
    b.bar_dt <= [end date]
GROUP BY 
    c.code_desc ) UnionTable

   group by UnionTable.code_description 

Answer 3

Make the union before you count:

SELECT
    COUNT(*) AS coded,
    x.code_desc
FROM (

  SELECT
    c.code_desc, f.foo_dt
  FROM 
    code c
    INNER JOIN assignedfoo af ON af.code_id = c.code_id
    INNER JOIN foo f ON f.foo_id = af.foo_id

  UNION ALL   

  SELECT
    c.code_desc, f.foo_dt
  FROM 
    code c
    INNER JOIN assignedbar ab ON ab.code_id = c.code_id
    INNER JOIN bar b ON b.bar_id = ab.bar_id

) x
WHERE
  x.foo_dt >= [start date] AND 
  x.foo_dt <= [end date]
GROUP BY 
  x.code_desc

Answer 4

When you do a union outside of your counts, you're "appending" rows to the first set. If you don't specify "all", it will attempt to return a DISTINCT set, thus two rows both containing "GOOD 1" would result in a single row "GOOD 1". If you do UNION ALL, it simply appends but doesn't add.

What you need to do is sum up over the union:

SELECT c.code_desc, s.COUNT(NUM)
FROM
Code c JOIN
(
    SELECT code_id, COUNT(*) AS NUM
      FROM AssignedFoo af
      JOIN Foo f on af.foo_id = f.foo_id
      WHERE f.foo_dt >= [start date]
        AND f.foo_dt <= [end date]
    GROUP BY CODE_ID
    UNION ALL
    SELECT CODE_ID, COUNT(*) AS NUM
      FROM AssignedBar ab
      JOIN Bar b on ab.bar_id = b.bar_id
      WHERE b.bar_dt >= [start date]
        AND b.bar_dt <= [end date]
    GROUP BY code_id
) s ON c.code_id = s.code_id
GROUP BY c.code_id