complicated count query - SQL Server Forums

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)

complicated count query

Author

Topic

dmikester1
Starting Member

2 Posts

Posted - 2008-09-30 : 10:50:57

This is complicated for me anyways. Here is the query I am trying to run: SELECT PRPID, count(PRPID) as countP, TDCVPG FROM TASDOCUMENT WHERE TDCNUM = '' AND TDCVPG <> '' AND COUNT(PRPID) > 1 GROUP BY PRPID

I want to show all records where TDCNUM is null, TDCVPG is not null and there is more than one of the same PRPID. Does this make sense?
Thanks
Mike

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-30 : 10:55:45

quote:
Originally posted by dmikester1

This is complicated for me anyways. Here is the query I am trying to run:
SELECT PRPID, count(PRPID) as countP,
TDCVPG FROM TASDOCUMENT
WHERE TDCNUM is null AND TDCVPG is not null
GROUP BY PRPID ,TDCVPG
Having count(PRPID)>1
I want to show all records where TDCNUM is null, TDCVPG is not null and there is more than one of the same PRPID. Does this make sense?
Thanks
Mike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 11:01:46

Just in case NULL should be treated the same way as en empty string, and just in case prpID could be NULL.

SELECT		prpID,
		COUNT(*) as countP
FROM		tasDocument
WHERE		(tdcNum IS NULL OR tdcNum = '')
		AND tdcVpg > ''
GROUP BY	prpID
HAVING		COUNT(*) > 1

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

dmikester1
Starting Member

2 Posts

Posted - 2008-09-30 : 11:03:32

quote:
Originally posted by sodeep

quote:
Originally posted by dmikester1

This is complicated for me anyways. Here is the query I am trying to run:
SELECT PRPID, count(PRPID) as countP,
TDCVPG FROM TASDOCUMENT
WHERE TDCNUM is null AND TDCVPG is not null
GROUP BY PRPID ,TDCVPG
Having count(PRPID)>1
I want to show all records where TDCNUM is null, TDCVPG is not null and there is more than one of the same PRPID. Does this make sense?
Thanks
Mike

Thanks for the fast reply. That doesn't work because I can't group by TDCVPG because those values are all different.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 11:04:51

[code]DECLARE @Sample TABLE
(
prpID INT,
tdcNum INT,
tdcVpg INT
)

INSERT @Sample
SELECT 1, NULL, 1 UNION ALL
SELECT 1, NULL, 2 UNION ALL
SELECT NULL, NULL, 1 UNION ALL
SELECT NULL, NULL, 2

-- sodeep
SELECT prpID,
COUNT(prpID) as countP
FROM @Sample
WHERE tdcNum IS NULL
AND tdcVpg IS NOT NULL
GROUP BY prpID,
tdcVpg
HAVING COUNT(prpID) > 1

-- Peso
SELECT prpID,
COUNT(*) as countP
FROM @Sample
WHERE (tdcNum IS NULL OR tdcNum = '')
AND tdcVpg > ''
GROUP BY prpID
HAVING COUNT(*) > 1[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources