How to calculate Duplicate name, ph no in Q result

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 to calculate Duplicate name, ph no in Q result

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2010-02-02 : 14:49:30

I wrote a Big Query which is a combination of 5 sub queries now in result i got the below result

CID-Name-Phone-KidsCount-CarCount
-------------------------------------------
100-AAA-2129082929-2-2
200-AAA-2129082929-1-1
300-BBB-7329082934-0-2
400-BBB-7329245645-2-1
500-CCC-6850082929-3-3
600-DDD-6850082929-4-1
-----
---------
-----------
-------------

so now i need to add another query which calculates duplicate name count and duplicate phone no count, so expected result will be

CID-Name-NAME COUNT-Phone-PHONE COUNT-KidsCount-CarCount
--------------------------------------------------------------------------------
100-AAA-2-2129082929 -2-2-2
200-AAA-2-2129082929-2-1-1
300-BBB-2-7329082934-1-0-2
400-BBB-2-7329245645-1-2-1
500-CCC-1-6850082929-2-3-3
600-DDD-1-6850082929-2-4-1

how can i add a query on top the above result to get this duplicate name & phone counts please help me

kindly give me any ideas sir

Thanks a ton in advance
dhani

dhani
Posting Yak Master

132 Posts

Posted - 2010-02-02 : 15:51:07

It may help ful to other, so i am posting it here, i got help from BRAD & ABDSAH

DECLARE @t TABLE (CID INT,Name VARCHAR(10),Phone BIGINT,KidsCount INT,CarCount INT)
INSERT INTO @t
SELECT 100,'AAA',2129082929,2,2 UNION ALL
SELECT 200,'AAA',2129082929,1,1 UNION ALL
SELECT 300,'BBB',7329082934,0,2 UNION ALL
SELECT 400,'BBB',7329245645,2,1 UNION ALL
SELECT 500,'CCC',6850082929,3,3 UNION ALL
SELECT 600,'DDD',6850082929,4,1

;WITH CTE AS
(
SELECT CID
,Name
,COUNT(Name) OVER (PARTITION BY Name) AS NameCount
,Phone
,COUNT(Phone) OVER (PARTITION BY Phone) AS PhoneCount
,KidsCount
,CarCount
FROM @T
GROUP BY CID ,Name ,Phone ,KidsCount ,CarCount
)

SELECT CONVERT(VARCHAR(10),CID )
+ '-' + Name
+ '-' + CONVERT(VARCHAR(10),NameCount)
+ '-' + CONVERT(VARCHAR(10),Phone)
+ '-' + CONVERT(VARCHAR(10),PhoneCount)
+ '-' + CONVERT(VARCHAR(10),KidsCount )
+ '-' + CONVERT(VARCHAR(10),CarCount ) AS
[CID-Name-NameCount-Phone-PhoneCount-KidsCount-CarCount]

FROM CTE
ORDER BY [CID-Name-NameCount-Phone-PhoneCount-KidsCount-CarCount]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources