Count Unique Values in Many To Many

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)

Count Unique Values in Many To Many

Author

Topic

support
Starting Member

4 Posts

Posted - 2010-02-10 : 14:07:23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 14:16:34

[code]
SELECT ColA,
COUNT(DISTINCT CASE WHEN PrevCnt = 0 THEN ColB ELSE NULL END) AS ColB
FROM
(
SELECT t.ColA,
t.ColB,
COALESCE(t1.PrevCnt,0) AS PrevCnt
FROM Table t
OUTER APPLY (SELECT COUNT(*) AS PrevCnt
FROM Table
WHERE ColA < t.ColA
AND ColB=t.ColB)t1
)r
GROUP BY ColA
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

support
Starting Member

4 Posts

Posted - 2010-02-10 : 15:34:30

I'm receiving an error which says, "Incorrect syntax near the keyword 'OUTER'. Here is my syntax:

SELECT GROUPNAME, COUNT(DISTINCT CASE WHEN PrevCnt = 0 THEN USERID ELSE NULL END) AS USERID
FROM (SELECT t .GROUPNAME, t .USERID, COALESCE (t1.PrevCnt, 0) AS PrevCnt
FROM USER_GROUP t OUTER APPLY
(SELECT COUNT(*) AS PrevCnt
FROM USER_GROUP
WHERE GROUPNAME < t .GROUPNAME AND USERID = t .USERID) t1) r
GROUP BY GROUPNAME

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-10 : 16:06:06

Your database need to be set to compatibility mode 90 or higher.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-10 : 16:11:24

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
colA INT,
colB VARCHAR(8)
)

INSERT @Sample
SELECT 1, 'Sam' UNION ALL
SELECT 1, 'Jim' UNION ALL
SELECT 1, 'Rita' UNION ALL
SELECT 2, 'Rita' UNION ALL
SELECT 2, 'Sam' UNION ALL
SELECT 2, 'Rick'

-- Keep it simple
SELECT colA,
COUNT(colB) AS colB
FROM (
SELECT MIN(colA) AS colA,
colB
FROM @Sample
GROUP BY colB
) AS d
GROUP BY colA[/code]

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

support
Starting Member

4 Posts

Posted - 2010-02-10 : 16:11:35

I'm sorry, I posted a SQL 2000 question in the 2005 thread. Is this possible in SQL 2000?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-10 : 16:13:29

Yes. See my previous "Keep it simple" post.

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

support
Starting Member

4 Posts

Posted - 2010-02-10 : 16:30:57

Looks to be working. Thanks!

I like to keep things simple.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-10 : 17:14:58

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
colA INT NOT NULL,
colB VARCHAR(8) NOT NULL,
UNIQUE (cola, colb)
)

INSERT @Sample
SELECT 1, 'Sam' UNION ALL
SELECT 1, 'Jim' UNION ALL
SELECT 1, 'Rita'UNION ALL
SELECT 2, 'Rita'UNION ALL
SELECT 2, 'Sam' UNION ALL
SELECT 2, 'Rick'UNION ALL
SELECT 3, 'Sam' UNION ALL
SELECT 4, 'Tara'UNION ALL
SELECT 6, 'Tara'UNION ALL
SELECT 6, 'Sara'

--My Query
SELECT cola, (SELECT COUNT(*)
FROM @sample s1
WHERE s1.cola = s.cola
AND NOT EXISTS
(SELECT *
FROM @Sample s2
WHERE s1.colb = s2.colb
AND s1.cola > s2.cola)) AS colb
FROM @sample s
GROUP BY cola
/*
cola colb
----------- -----------
1 3
2 1
3 0
4 1
6 1
*/

-- Peso Query
-- Keep it simple
SELECT colA,
COUNT(*) AS colB
FROM (SELECT MIN(colA) AS colA,
colB
FROM @Sample
GROUP BY colB) AS d
GROUP BY colA

/*
colA colB
----------- -----------
1 3
2 1
4 1
6 1
*/
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-10 : 18:02:46

The only difference is that you display the {3, 0} record?

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-10 : 18:05:58

yes, an important difference

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-10 : 18:12:34

Is it requested by OP? I didn't see it.
If so, add this to the derived table d

			UNION ALL

			SELECT		colA,
					NULL
			FROM		@Sample
			GROUP BY	colA

and it uses only 4 reads and your suggestion need 32 reads for this small sample data.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources