CODEWITHSUNDEEP
sqlsql-server-2008countsumunion
Matthew Hait
Matthew Hait

Reputation: 65

SQL Count across multiple tables

I need to find the number of occurrences per unique combination of x & y across two tables.

  Table1:        Table2:
+----+----+    +----+----+
| x  | y  |    | x  | y  |
+----+----+    +----+----+
| 20 | 10 |    | 20 | 10 |
| 20 | 20 |    | 20 | 20 |
| 20 | 20 |    | 30 | 20 |
| 40 | 10 |    +----+----+
+----+----+

Output:

+----+----+--------+
| x  | y  | amount |
+----+----+--------+
| 20 | 10 |      2 |
| 20 | 20 |      3 |
| 30 | 20 |      1 |
| 40 | 10 |      1 |
+----+----+--------+

Here's my current query:

SELECT x, Y, count(*) AS Amount  FROM Table1 
UNION ALL
SELECT X, Y, count(*) AS Amount  FROM Table2 
GROUP BY x, y;

This creates duplicate instances of unique combinations of x & y.

Upvotes: 1

Views: 68

Answers (2)

Jacobm001
Jacobm001

Reputation: 4539

I would set up something like this:

select
  d.x
  , d.y
  , sum(d.cnt) as cnt
from
  (
    select x, y, count(*) as cnt from table1 group by x, y
    union all
    select x, y, count(*) as cnt from table2 group by x, y
  ) d
group by
  d.x
  , d.y

This solution counts the unique entries in both tables and then adds the results together from the two tables.

Upvotes: 0

Raging Bull
Raging Bull

Reputation: 18747

Apply COUNT() on the result of UNION:

select x,y,count(*) as Amount
from
(SELECT x, Y FROM Table1 
 UNION ALL
 SELECT X, Y FROM Table2)temp
group by x,y

Upvotes: 1

Related Questions