CODEWITHSUNDEEP
sqlsql-serversql-server-2008
aSystemOverload
aSystemOverload

Reputation: 3084

Construct SQL Statement to retrieve count() of specific records in same/different table

I did previously ask a question about this, but must admit was biased against SQL based solutions and now I can't find the question, so I'll try again, phrase it carefully and be much more open to any solutions provided.

I have two tables:

tblCurrent 
 Ref  | CustomerID | ... .. .. .  . |
X001
X002
X003


tblHistorical 
 Ref   | ... .. .. .  . |  Missing | Matched
X001   | ... .. .. .  . |  TRUE    | FALSE
X001   | ... .. .. .  . |  FALSE   | FALSE
X002   | ... .. .. .  . |  TRUE    | TRUE
X002   | ... .. .. .  . |  TRUE    | FALSE
X003   | ... .. .. .  . |  FALSE   | FALSE
X003   | ... .. .. .  . |  TRUE    | TRUE

Ref is unique in tblCurrent, but not in Historical.

How can I construct a view, that is based on tblCurrent that results in three additional columns that count the number of records in tblHistorical that:

Note that I will need to add additional columns that count the number of records in tblHistorical based on similar criteria.

Upvotes: 1

Views: 131

Answers (4)

Joe G Joseph
Joe G Joseph

Reputation: 24046

SELECT T.REF,
        COUNT(CASE WHEN H.MISSING = 'TRUE' THEN 1  END) AS MISSINGTRUE,
        COUNT(CASE WHEN H.MISSING = 'FALSE' THEN 1 END) AS MISSINGFALSE,
        COUNT(CASE WHEN H.MATCHED = 'TRUE' THEN 1  END) AS MATCHEDTRUE
FROM   TBLREF T 
JOIN   TBLHISTORICAL H
ON     R.REF = H.REF
GROUP BY T.REF

Upvotes: 1

swingNoobie
swingNoobie

Reputation: 151

SELECT R.Ref,
SUM(CASE WHEN H.Missing = TRUE THEN 1 ELSE 0 END) as MissingTrue,
SUM(CASE WHEN H.Missing = FALSE THEN 1 ELSE 0 END) as MissingFalse,
SUM(CASE WHEN H.Matched = TRUE THEN 1 ELSE 0 END) as MatchedTrue
FROM tblRef R 
INNER JOIN tblHistorical H
ON R.Ref = H.Ref
Group by R.Ref

Upvotes: 1

John Woo
John Woo

Reputation: 263703

I think you need to use LEFT JOIN on this since it is possible that sometime ref may not exist on tblHistorical table.

SELECT  a.ref,
        SUM(CASE WHEN b.Missing = 'True' THEN 1 ELSE 0 END) missingTrue,
        SUM(CASE WHEN b.Missing = 'False' THEN 1 ELSE 0 END) missingFalse,
        SUM(CASE WHEN b.Matched = 'True' THEN 1 ELSE 0 END) matchTrue
FROM    tblCurrent a
        LEFT JOIN tblHistorical b
            on a.ref = b.ref
GROUP BY a.ref

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33476

SELECT R.Ref,
SUM(CASE WHEN H.Missing = TRUE THEN 1 ELSE 0 END) as MissingTrue,
SUM(CASE WHEN H.Missing = FALSE THEN 1 ELSE 0 END) as MissingFalse,
SUM(CASE WHEN H.Matched = TRUE THEN 1 ELSE 0 END) as MatchedTrue
FROM tblRef R JOIN tblHistorical H
ON R.Ref = H.Ref

Upvotes: 3

Related Questions