CODEWITHSUNDEEP
sqlsql-server
user1773949
user1773949

Reputation: 119

Group By preventing Having clause from giving desired results

This is quite straight-forward but I'm having trouble populating the temp table where the number of distinct values in TypeRec is not greater than one per MatchId and RefNum fields. 

CREATE TABLE #TempTable (MatchId int, RefNum char(50), TypeRec char(50))  

INSERT INTO #TempTable (MatchId, RefNum, TypeRec)
SELECT   t.MatchId,
t.refNum,
t.TypeRec
FROM Transaction t (nolock) 
WHERE t.UserText = 'Proposed'    
AND t.CompanyId = 4  
AND t.RefNum = 'CreditCard'  
GROUP BY t.matchId, t.RefNum, t.TypeRec
HAVING count(distinct t.TypeRec) = 1

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Just remove typerec from the group by and use min() or max():

INSERT INTO #TempTable (MatchId, RefNum, TypeRec)
    SELECT   t.MatchId, t.refNum, min(t.TypeRec)
    FROM Transaction t (nolock) 
    WHERE t.UserText = 'Proposed'    
    AND t.CompanyId = 4  
    AND t.RefNum = 'CreditCard'  
    GROUP BY t.matchId, t.RefNum
    HAVING count(distinct t.TypeRec) = 1;

This counts the number of TypeRecs for each combination of MatchId and RefNum. It only returns such pairs when there is one TypeRec. The use min() is just to get that value -- the minimum of one value is that value.

Upvotes: 2

Related Questions