| Author |
 Topic  |
|
|
Dani_87
Starting Member
Australia
14 Posts |
 Posted - 05/07/2012 : 21:53:24
|
Trying to count IDs from two tables.
TABLE 1
ID UNI
1 3
1 4
1 6
2 3
2 9
4 11
TABLE 2
ID UNI
1 9
1 5
2 10
3 4
3 7
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)
Singapore
16746 Posts |
Posted - 05/07/2012 : 21:59:09
|
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
Time is always against us
|
 |
|
|
Dani_87
Starting Member
Australia
14 Posts |
Posted - 05/07/2012 : 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
Time is always against us
That the only method khtan? |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 05/07/2012 : 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
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 05/07/2012 : 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
Time is always against us
|
Edited by - khtan on 05/07/2012 22:28:28 |
|
|
|
Dani_87
Starting Member
Australia
14 Posts |
Posted - 05/08/2012 : 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
Time is always against us
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)
Singapore
16746 Posts |
Posted - 05/08/2012 : 00:07:40
|
select ID, max(UNI)
from
(
select ID, UNI from t1
union all
select ID, UNI from t2
) t
group by ID
KH
Time is always against us
|
|
|
| |
Topic |
|
|
|
Count from 2 tables
