CODEWITHSUNDEEP
sqljoinsql-server-2008-r2union
user1151923
user1151923

Reputation: 1872

SQL union same number of columns, same data types, different data

I have two result sets that look approximately like this:

Id       Name    Count
1        Asd     1  
2        Sdf     4    
3        Dfg     567    
4        Fgh     23

But the Count column data is different for the second one and I would like both to be displayed, about like this:

Id       Name    Count from set 1    Count from set two
1        Asd     1                   15
2        Sdf     4                   840
3        Dfg     567                 81
4        Fgh     23                  9

How can I do this in SQL (with union if possible)? My current SQL, hope this will better explain what I want to do:

(SELECT Id, Name, COUNT(*) FROM Customers where X)
union
(SELECT Id, Name, COUNT(*) FROM Customers where Y)

Upvotes: 0

Views: 605

Answers (2)

podiluska
podiluska

Reputation: 51494

select * 
from
(
    SELECT 'S1' as dataset, Id, Name, COUNT(*) as resultcount FROM Customers where X
    union
    SELECT 'S2',Id, Name, COUNT(*) FROM Customers where Y
) s
pivot
(sum(resultcount) for dataset in (s1,s2)) p

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can do something like:

;WITH Unioned
AS
(
    SELECT 'Set1' FromWhat, Id, Name FROM Table1
    UNION ALL
    SELECT 'Set2', Id, Name FROM Table2
)
SELECT 
  Id, 
  Name,
  SUM(CASE FromWhat WHEN 'Set1' THEN 1 ELSE 0 END) 'Count from set 1',
  SUM(CASE FromWhat WHEN 'Set2' THEN 1 ELSE 0 END) 'Count from set 2'
FROM Unioned
GROUP BY Id, Name;

SQL Fiddle Demo

Upvotes: 1

Related Questions