a q about counting

[DE]

Hi,

New to SQL. Got some questions about it.

Suppose I have two tables. Each of them has a single column, named as
c1. For table T1, I have:
1
1
1
3
3
5
7
9
For table T2, I have:
1
2
3
4
5
1
3
The exercise I want to do is to select the number of occurence in T1
for those elements in T2. For above tables, I want to show:
1 3 ( i.e. "1" is in T2 and shows 3 times in T1)
2 0 (i.e. "2" is in T2 but doesn't show in T1)
3 2 (i.e. "3" is in T2 and show 2 times in T1)

It seems I can't figure out a good way to do this. Any help will be
appreciated.

Thanks

[Erland Sommarskog]

DE (oo**@hotmail.com) writes:

Suppose I have two tables. Each of them has a single column, named as
c1. For table T1, I have:
1
1
1
3
3
5
7
9
For table T2, I have:
1
2
3
4
5
1
3
The exercise I want to do is to select the number of occurence in T1
for those elements in T2. For above tables, I want to show:
1 3 ( i.e. "1" is in T2 and shows 3 times in T1)
2 0 (i.e. "2" is in T2 but doesn't show in T1)
3 2 (i.e. "3" is in T2 and show 2 times in T1)

SELECT T2.c1, coalesce(COUNT(T1.c1), 0)
FROM T2
LEFT JOIN T1 ON T2.c1 = T1.c1
GROUP BY T2.c1

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Gert-Jan Strik]

I will give you a different solution for your homework, so you will
still have to think for yourself (which should I take? and why?):

SELECT c1, COUNT(*)
FROM T1
GROUP BY c1

UNION ALL

SELECT DISTINCT c1, 0
FROM T2
WHERE NOT EXISTS (
SELECT *
FROM T1
WHERE T1.c1 = T2.c1
)
ORDER BY c1

HTH,
Gert-Jan
DE wrote:

>
Hi,

New to SQL. Got some questions about it.

Suppose I have two tables. Each of them has a single column, named as
c1. For table T1, I have:
1
1
1
3
3
5
7
9
For table T2, I have:
1
2
3
4
5
1
3
The exercise I want to do is to select the number of occurence in T1
for those elements in T2. For above tables, I want to show:
1 3 ( i.e. "1" is in T2 and shows 3 times in T1)
2 0 (i.e. "2" is in T2 but doesn't show in T1)
3 2 (i.e. "3" is in T2 and show 2 times in T1)

It seems I can't figure out a good way to do this. Any help will be
appreciated.

Thanks