SELECT - CASE - GROUP BY

Question

SELECT - CASE - GROUP BY

carl6885

SSC Journeyman

Points: 94
More actions
August 12, 2012 at 2:21 pm

#265819

Hi
I am new to this forum, so hopefully im in the right place and thank you in advance.
Overview: An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).
I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.
If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
The scripts for creating the two tables are as follows:
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
}
tblPolicyNumbers
----------------------
CREATE TABLE tblPolicyNumbers_ER
{
PolicyNumberID int,
ErrorID int,
PolicyNumber varchar(10)
}
My ASP.Net page is titled Dashboard.aspx which contains the Gridview - I configure the datasource using the smart tags. When given the option I write a custom SQL string.
My first SQL string was:
SELECT tblErrors_ER.ErrorID, tblErrors_ER.ErrorType, tblErrors_ER.DateLogged, CASE WHEN Count(*) = 1 THEN tblPolicyNumbers.PolicyNumber ELSE 'MULTIPLE' END
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, tblPolicyNumbers_ER.PolicyNumbers.PolicyNumbers
This generates the following results in the GridView:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 1234567xx
2 Test 08/08/2012 123458xx
2 Test 08/08/2012 999999xx
The desired results would be:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 1234567xx
2 Test 08/08/2012 Multiple
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
Any help would be greatly appreciated.
Thanks
Carl

Viewing 6 posts - 1 through 5 (of 5 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