SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Count by group
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

155 Posts

Posted - 07/16/2013 :  16:03:41  Show Profile  Reply with Quote
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


Edited by - evanburen on 07/16/2013 16:10:59

ScottPletcher
Constraint Violating Yak Guru

USA
335 Posts

Posted - 07/16/2013 :  16:18:29  Show Profile  Reply with Quote


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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/17/2013 :  04:52:52  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000