Count by group - 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 2008 Forums

Transact-SQL (2008)

Count by group

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2013-07-16 : 16:03:41

I have data like this

BorrowerNumber TagName
1 bPCARecl
1 bThisOne
1 bThatOne
2 bThisOne
2 bThatOne
2 bPCARecl
3 bThisOne
3 bThatOne
4 bPCARecl

I want to find individual Borrower Numbers where any one of the group <> 'bPCARecl'

DISTINCT lists every row where tag name <> 'bPCARecl' but it doesn't tell me if any one row for a group of borrower numbers <> 'bPCARecl'

My code returns only rows where there is only one record (4 in my example data)

SELECT BorrowerNumber
FROM Admins
WHERE TagName <> 'bPCARecl'
GROUP BY BorrowerNumber
HAVING (Count(TagName)=1)

I would want the results to return BorrowerNumber 3 it is the only one none of the rows in that group of Borrower Numbers does not contain TagName of 'bPCARecl'

Thanks

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-07-16 : 16:18:29

[code]

SELECT BorrowerNumber
FROM dbo.Admins
GROUP BY BorrowerNumber
HAVING MAX(CASE WHEN TagName = 'bPCARecl' THEN 1 ELSE 0 END) = 0

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 04:52:52

if you want entire columns to be returned in resultset you need something like this


SELECT BorrowerNumber, TagName
FROM
(
SELECT *,SUM(CASE WHEN TagName = 'bPCARecl' THEN 1 ELSE 0 END) OVER (PARTITION BY BorrowerNumber) AS Cnt
FROM dbo.Admins
)t
WHERE Cnt = 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources