SELECT - CASE - GROUP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT - CASE - GROUP

Author

Topic

carl6885
Starting Member

2 Posts

Posted - 2012-08-12 : 16:18:16

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-12 : 17:31:53

[code]
SELECT tblErrors_ER.ErrorID, tblErrors_ER.ErrorType, tblErrors_ER.DateLogged,
CASE WHEN Count(tblPolicyNumbers.PolicyNumber) = 1 THEN MAX(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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

carl6885
Starting Member

2 Posts

Posted - 2012-08-13 : 01:07:35

Thank you so much!! Cant believe all I was missing was the MAX(XXX), MIN(XXX) also works!! Thank you!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 10:11:42

welcome. yep both will work so far as you've single value within a group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources