how can i count the duplicate column's value

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

SQL Server 2005 Forums

Transact-SQL (2005)

how can i count the duplicate column's value

Author

Topic

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-05 : 08:32:59

i have two tables.

for example 'A' and 'B'.

'A' contain 'id','name' and 'B' contain 'id','name'.

A
id name
1 a
2 a
3 b
4 a

B
id name
1 r
2 a
3 b
4 a

i want to count its repeatition.
and want to compare from 'id' and 'name' of 'A' (table) with 'id' and 'name' of 'B'(table).

so it should be 2.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-05 : 08:56:03

What should be 2?

No, you're never too old to Yak'n'Roll if you're too young to die.

apodemus
Starting Member

30 Posts

Posted - 2010-05-05 : 09:00:26

select count(*) from a join b on a.id = b.id and a.name = b.name

apodemus

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-05 : 09:03:18

Sorry apodemus but your query will return 3 not 2, and as webfred said 2 calculation is not clear. So provide more information please.

apodemus
Starting Member

30 Posts

Posted - 2010-05-05 : 09:11:42

i know it's 3, but i wrote solution for maifs question, suggested answer "2" could be wrong :), or example is wrong......if not I don't know what maifs needs

apodemus

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-05 : 09:19:52

[code]SELECT ID,
Name,
COUNT(*)
FROM (
SELECT ID,
Name
FROM TableA

UNION ALL

SELECT ID,
Name
FROM TableB
) AS d
GROUP BY ID,
Name
HAVING COUNT(*) > 1[/code]

N 56°04'39.26"
E 12°55'05.63"

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-05 : 09:34:26

[code]
SELECT id, name, 2 as count
FROM
(SELECT id, name
FROM table_1
INTERSECT
SELECT id, name
FROM table_2) D
[/code]

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-05-05 : 23:53:13

yes it should be count as 2 (a) and 1(b).
2 a
3 b
4 a

apodemus
Starting Member

30 Posts

Posted - 2010-05-06 : 07:50:07