problem in joining results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem in joining results

Author

Topic

rajeshdbabu
Starting Member

2 Posts

Posted - 2012-01-16 : 12:26:24

hi experts,

i am newbie, please help me with my sql query

table1
id X
1 1
2 1
3 1
1 1
3 1

table2
id Y
1 1
2 1
4 1
5 1
2 1

required output
id X Y
1 2 1
2 1 2
3 2 0
4 0 1
5 0 1

my following query produces wrong output, please help me to correct/rewrite it

select id, count(X) from table1 group by id, X
union
select id, count(Y) from table1 group by id, X

many thanks
Raj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 12:30:34

[code]
select coalesce(m.id,n.id) as id,
coalesce(m.cnt,0) AS [X],
coalesce(n.cnt,0) AS [Y]
from
(
select id, count(X) as cnt from table1 group by id
)m
full outer join
(
select id, count(Y) from table1 group by id
)n
on n.id = m.id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajeshdbabu
Starting Member

2 Posts

Posted - 2012-01-17 : 10:56:37

Hi Visakh, Thank you very much for your helpful reply, Raj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources