SELECT - CASE - GROUP BY

Question

SELECT - CASE - GROUP BY

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 1

I'm on a computer that don't SQL Server installed but you could try to use COUNT in an inner SELECT and then in an outer SELECT you could use something like (CASE WHEN total > 1 THEN 'MULTIPLE' ELSE 'SOMETHING ELSE' END). The "total" column would be the count result of the inner SELECT.

I hope I have made myself understandable enough. 😀

See if it works. 🙂

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 2

Does this work?

SELECT ta.ErrorID, tab.ErrorType, tab.DateLogged,

CASE WHEN total = 1 THEN tab.PolicyNumber ELSE 'MULTIPLE' END

FROM (SELECT tblErrors_ER.ErrorID, tblErrors_ER.ErrorType, tblErrors_ER.DateLogged,

MIN(tblPolicyNumbers.PolicyNumber) AS PolicyNumber,

COUNT(1) AS total

FROM tblErrors_ER

INNER JOIN tblPolicyNumbers_ER

ON tblErrors_ER.ErrorID = tblPolicyNumbers_ER.ErrorID

GROUP BY tblErrors_ER.ErrorID, tblErrors_ER.ErrorType, tblErrors_ER.DateLogged) tab

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

carl6885 SSC Journeyman Points: 94 More actions · Answer 3

Thanks for the help! Got it working with:

SELECT tblErrors_ER.ErrorID, CASE WHEN Count(tblPolicyNumbers_ER.PolicyNumber) = 1 THEN MIN(tblPolicyNumbers_ER.PolicyNumber) ELSE 'Multiple' END

FROM tblErrors_ER

INNER JOIN tblPolicyNumbers_ER ON

tblErrors_ER.ErrorID = tblPolicyNumbers_ER.ErrorID

GROUP BY tblErrors_ER.ErrorID

Thanks

Carl

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 4

Nice. That's almost the same thing without using a derived table.

I tend to use it whenever possible (or even CTEs) to make the code more readable. 🙂

Have a great week!

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

carl6885 (8/12/2012)
Thanks for the help! Got it working with:
SELECT tblErrors_ER.ErrorID, CASE WHEN Count(tblPolicyNumbers_ER.PolicyNumber) = 1 THEN MIN(tblPolicyNumbers_ER.PolicyNumber) ELSE 'Multiple' END
FROM tblErrors_ER
INNER JOIN tblPolicyNumbers_ER ON
tblErrors_ER.ErrorID = tblPolicyNumbers_ER.ErrorID
GROUP BY tblErrors_ER.ErrorID
Thanks
Carl

Hi Carl

Here's the same code using table aliases - it don't 'arf make it more readable 😉

SELECT

e.ErrorID,

CASE WHEN Count(pn.PolicyNumber) = 1 THEN MIN(pn.PolicyNumber) ELSE 'Multiple' END

FROM tblErrors_ER e

INNER JOIN tblPolicyNumbers_ER pn

ON e.ErrorID = pn.ErrorID

GROUP BY e.ErrorID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden