CODEWITHSUNDEEP
sqlselectjoin
Mance
Mance

Reputation: 79

Join two Tables and add Count result

I have 2 Tables A & B

A

select TranType, DocType, Document  FROM
db1.dbo.A

TRANTYPE | DOCTYPE  | DOCUMENT
Coup     | Stat     | 1
Coup     | Stat     | 2
Coup     | Stat     | 3
Coup     | Stat     | ...
Coup     | Stat     | 100
Swp      | Corr     | 1
Swp      | Corr     | 2
Swp      | Corr     | ...
Swp      | Corr     | 5

B ...

A:

SELECT  TranType, DocType, COUNT (*) AS Docs 
FROM db1.dbo.A 
GROUP BY TranType, DocType

TRANTYPE | DOCTYPE  | DOCS
Coup     | Stat     | 100
Swp      | Corr     | 5

B:

SELECT  RecType, SubType, COUNT (*) AS Docs 
FROM db2.dbo.B 
GROUP BY RecType, SubType

RECTYPE  | SUBTYPE  | DOCS
Coup     | Stat     | 50
Cr       | Cr       | 3
Swp      | Cr       | 10

I managed to get the intersection of the 2 Tables and Add up the Count result:

A ∩ B

SELECT a.TranType, a.DocType,
     (SELECT COUNT(*) FROM db2.dbo.B b WHERE b.RecType= a.TranType AND b.SubType = a.DocType) +
     COUNT(a.TranType) AS SUM
FROM db1.dbo.A a 
GROUP BY a.TranType, a.DocType 
ORDER BY TranType

TRANTYPE | DOCTYPE  |  SUM
Coup     | Stat     |  150

Anyone got an idea how to get all the entries (Union)?

TRANTYPE | DOCTYPE  | DOCS
Coup     | Stat     | 150    <----
Swp      | Corr     | 5
Cr       | Cr       | 3
Swp      | Cr       | 10

SOLUTION: I had different collation on my tables, resulting in an error.

Find the solution with proper collation handling here: SQL Sample Fiddle

Upvotes: 0

Views: 89

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would suggest using subqueries and then aggregating:

select TranType, DocType, sum(docs) as docs
from ((select TranType, DocType, count(*) as docs
       from db1.dbo.A a
       group by TranType, DocType
      ) union all
      (select RecType, SubType, count(*) as docs
       from db1.dbo.B
       group by RecType, SubType
      )
     ) ab
group by TranType, DocType;

You can also use a full outer join, but then you have to deal with NULL values in the final result.

Note: this will return all pairs, even those in only one table.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

SQL Fiddle Demo

UNION is easy. Just need matching column name

Or UNION ALL to keep duplicated 

SELECT TranType, DocType, SUM (Docs)
FROM (
      SELECT  TranType, DocType, COUNT (*) AS Docs 
      FROM TableA
      GROUP BY TranType, DocType
      UNION ALL
      SELECT  RecType as TranType, SubType as DocType, COUNT (*) AS Docs 
      FROM TableB 
      GROUP BY RecType, SubType
     ) T
GROUP BY TranType, DocType
ORDER BY TranType, DocType;

OUTPUT

| TranType | DocType |     |
|----------|---------|-----|
|     Coup |    Stat | 150 |
|       Cr |      Cr |   3 |
|      Swp |    Corr |   5 |
|      Swp |      Cr |  10 |

EDIT

Your first query should be write like this to avoid perfom a subquery for each row.

SELECT TranType, DocType, SUM (Docs)
FROM (
      SELECT  TranType, DocType, COUNT (*) AS Docs 
      FROM TableA
      GROUP BY TranType, DocType
      UNION ALL
      SELECT  RecType as TranType, SubType as DocType, COUNT (*) AS Docs 
      FROM TableB 
      GROUP BY RecType, SubType
     ) T
GROUP BY TranType, DocType
HAVING COUNT(*) > 1;

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

All solutions till now just overcomplicating the problem. Also do you want counts or sums?

SELECT TranType, DocType, SUM(DOCS) DOCS--or COUNT(DOCS) DOCS
FROM (SELECT TranType, DocType, DOCS FROM A
      UNION ALL
      SELECT RecType, SubType, DOCS FROM B) t
GROUP BY TranType, DocType

Upvotes: 0

Hogan
Hogan

Reputation: 70523

Do you want a cross join?

SELECT * 
FROM (
  SELECT  TranType, DocType, COUNT (*) AS ACount 
  FROM db1.dbo.A 
  GROUP BY TranType, DocType
) A, (
  SELECT  RecType, SubType, COUNT (*) AS BCount
  FROM db2.dbo.B 
  GROUP BY RecType, SubType
) B
WHERE A.DocType = B.SubType

Upvotes: 0

Related Questions