Count from 2 tables - 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

Count from 2 tables

Author

Topic

Dani_87
Starting Member

14 Posts

Posted - 2012-05-07 : 21:53:24

Trying to count IDs from two tables.

TABLE 1

TABLE 2

How would i count the ID individualy from each table
to get:

ID 	CNT_T1	CNT_T2
1	3	2
2	2	1
3	NULL	2
4	1	NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-07 : 21:59:09

[code]
select ID, CNT_T1 = sum(CNT_T1), CNT_T2 = sum(CNT_T2)
from
(
select ID, CNT_T1 = count(*), CNT_T2 = NULL
from t1
group by ID
union all
select ID, CNT_T1 = NULL, CNT_T2 = count(*)
from t2
group by ID
) t
group by ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Dani_87
Starting Member

14 Posts

Posted - 2012-05-07 : 22:11:39

quote:

Originally posted by khtan


select	ID, CNT_T1 = sum(CNT_T1), CNT_T2 = sum(CNT_T2)
from
(
	select	ID, CNT_T1 = count(*), CNT_T2 = NULL
	from	t1
	group by ID
	union all
	select	ID, CNT_T1 = NULL, CNT_T2 = count(*)
	from	t2
	group by ID
) t
group by ID

KH
[spoiler]Time is always against us[/spoiler]

That the only method khtan?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-07 : 22:17:39

nope. Here is another method


select	ID 	= coalesce(t1.ID, t2.ID),
	CNT_T1,
	CNT_T2
from	(
		select	ID, CNT_T1 = count(*)
		from	t1
		group by ID
	) t1
full outer join
	(
		select	ID, CNT_T2 = count(*)
		from	t2
		group by ID
	) t2	on	t1.ID	= t2.ID

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-07 : 22:27:45

Yet another method, but you will get 0 instead of NULL if the ID does not exists


select	ID, CNT_T1 = count(CNT_T1), CNT_T2 = count(CNT_T2)
from
(
	select	ID, CNT_T1 = UNI, CNT_T2 = NULL
	from	t1
	union all
	select	ID, CNT_T1 = NULL, CNT_T2 = UNI
	from	t2
) t
group by ID

you can use nullif() if you wanted to show NULL instead of 0

KH
[spoiler]Time is always against us[/spoiler]

Dani_87
Starting Member

14 Posts

Posted - 2012-05-08 : 00:04:18

quote:
Originally posted by khtan

Yet another method, but you will get 0 instead of NULL if the ID does not exists

select ID, CNT_T1 = count(CNT_T1), CNT_T2 = count(CNT_T2)
from
(
select ID, CNT_T1 = UNI, CNT_T2 = NULL
from t1
union all
select ID, CNT_T1 = NULL, CNT_T2 = UNI
from t2
) t
group by ID

you can use nullif() if you wanted to show NULL instead of 0

KH
[spoiler]Time is always against us[/spoiler]

Thank you, very helpful

Another question if you have time.
With the same 2 tables above i want to find the maximum UNI for that particular ID.

So ID 1 has UNI 3,4,5,6,9 so max is 9 (FOR ID 1)
ID 2 has UNI 3,9,10 so max is 10 (FOR ID 2)
ID 3 has UNI 4,7 so max is 7 (FOR ID 3)
etc.
I am hving problems matching it because i cant make ID form Table 1 equal ID from table 2 because id 3 and 4 dont appear in both.

to GET:

ID	UNI
1	9
2	10
3	7
4        11

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-08 : 00:07:40

[code]
select ID, max(UNI)
from
(
select ID, UNI from t1
union all
select ID, UNI from t2
) t
group by ID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources