Alternative for sum - 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 2008 Forums

Transact-SQL (2008)

Alternative for sum

Author

Topic

Bertin
Starting Member

7 Posts

Posted - 2011-04-06 : 07:34:26

The next query worked fine because score always had a integer value. Now someone decided to add DDM5 with a score A,B,C,D or E. The table stores it fine because Score is a varchar column. The cast (Score AS bigint) does not work anymore. How can I solve this.
Note that at a maximum I only have one row per clientcaseID because of the rank = 1 in the where clause

select clientcaseID,
SUM(CASE WHEN name = '3DM1' THEN cast(Score AS bigint) END) AS DDM1,
SUM(CASE WHEN name = '3DM2' THEN cast(Score AS bigint) END) AS DDM2,
SUM(CASE WHEN name = '3DM3' THEN cast(Score AS bigint) END) AS DDM3,
SUM(CASE WHEN name = '3DM4' THEN cast(Score AS bigint) END) AS DDM4
FROM TestResults
WHERE TestResults.Rank = 1
GROUP BY clientCaseId

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-04-06 : 07:36:31

post some records.

Raghu' S

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-06 : 07:48:26

[code]SELECT ClientCaseID,
SUM(CASE WHEN Name = '3DM1' THEN CAST(Score AS BIGINT) END) AS DDM1,
SUM(CASE WHEN Name = '3DM2' THEN CAST(Score AS BIGINT) END) AS DDM2,
SUM(CASE WHEN Name = '3DM3' THEN CAST(Score AS BIGINT) END) AS DDM3,
SUM(CASE WHEN Name = '3DM4' THEN CAST(Score AS BIGINT) END) AS DDM4
FROM dbo.TestResults
WHERE [RANK] = 1
AND Score NOT LIKE '%[^0-9]%'
GROUP BY ClientCaseID[/code]

N 56°04'39.26"
E 12°55'05.63"

Bertin
Starting Member

7 Posts

Posted - 2011-04-06 : 08:29:36

What I realize from the responds that I did not express the problem correct:

with table like
clientCaseId | Name | Score | Rank
6 , 3DM1, 5 , 1
6 , 3DM2, 15 , 1
6 , 3DM3, 52 , 1
6 , 3DM4, 37 , 1
8 , 3DM1, 41, 1
8 , 3DM2, 90, 1
8 , 3DM3, 19, 1
8 , 3DM4, 70, 1
14, 3DM1, 21, 1
14, 3DM2, 41, 1

the next query works fine

select clientcaseID,
SUM(CASE WHEN name = '3DM1' THEN cast(Score AS bigint) END) AS DDM1,
SUM(CASE WHEN name = '3DM2' THEN cast(Score AS bigint) END) AS DDM2,
SUM(CASE WHEN name = '3DM3' THEN cast(Score AS bigint) END) AS DDM3,
SUM(CASE WHEN name = '3DM4' THEN cast(Score AS bigint) END) AS DDM4
FROM TestResults
WHERE TestResults.Rank = 1
GROUP BY clientCaseId

and the output is then a table (pivot table)

clientcaseID | 3DM1 | 3DM2 | 3DM3 | 3DM4
with data like

6, 5, 15, 52, 37
8, 41, 90, 19, 70
14, 21, 41, .., ..

Now someone decided to add a new test with the name 3DM5. The scores of this test is not a value but A, B, C, D or E

This results in records in the first table like
clientCaseId | Name | Score | Rank
6 , 3DM1, 5 , 1
6 , 3DM2, 15 , 1
6 , 3DM3, 52 , 1
6 , 3DM4, 37 , 1
6 , 3DM5, B, 1

I'd like to have the result of this new test also in my pivot table like

clientcaseID | 3DM1 | 3DM2 | 3DM3 | 3DM4 | 3DM5
with data like

6, 5, 15, 52, 37, B
8, 41, 90, 19, 70, C
14, 21, 41, .., .., A

But I cannot do this with SUM because A, B,C etc is not numeric. Is there another option to achieve this? It also enough when in the result pivot table A is replaced by 1, B by 2 etc.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 08:57:56

just use Min() or Max() instead...

select clientcaseID,
SUM(CASE WHEN name = '3DM1' THEN cast(Score AS bigint) END) AS DDM1,
SUM(CASE WHEN name = '3DM2' THEN cast(Score AS bigint) END) AS DDM2,
SUM(CASE WHEN name = '3DM3' THEN cast(Score AS bigint) END) AS DDM3,
SUM(CASE WHEN name = '3DM4' THEN cast(Score AS bigint) END) AS DDM4,
MAX(CASE WHEN name = '3DM5' THEN Score END) AS DDM4
FROM TestResults
WHERE TestResults.Rank = 1
GROUP BY clientCaseId

Corey

I Has Returned!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-06 : 09:19:58

quote:
Originally posted by Seventhnight

just use Min() or Max() instead...

select clientcaseID,
SUM(CASE WHEN name = '3DM1' THEN cast(Score AS bigint) END) AS DDM1,
SUM(CASE WHEN name = '3DM2' THEN cast(Score AS bigint) END) AS DDM2,
SUM(CASE WHEN name = '3DM3' THEN cast(Score AS bigint) END) AS DDM3,
SUM(CASE WHEN name = '3DM4' THEN cast(Score AS bigint) END) AS DDM4,
MAX(CASE WHEN name = '3DM5' THEN Score END) AS DDM45
FROM TestResults
WHERE TestResults.Rank = 1
GROUP BY clientCaseId

Corey

I Has Returned!!

No, you're never too old to Yak'n'Roll if you're too young to die.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 10:13:37

Yeah, yeah...

quote:
Originally posted by webfred

quote:
Originally posted by Seventhnight

just use Min() or Max() instead...

select clientcaseID,
SUM(CASE WHEN name = '3DM1' THEN cast(Score AS bigint) END) AS DDM1,
SUM(CASE WHEN name = '3DM2' THEN cast(Score AS bigint) END) AS DDM2,
SUM(CASE WHEN name = '3DM3' THEN cast(Score AS bigint) END) AS DDM3,
SUM(CASE WHEN name = '3DM4' THEN cast(Score AS bigint) END) AS DDM4,
MAX(CASE WHEN name = '3DM5' THEN Score END) AS DDM45
FROM TestResults
WHERE TestResults.Rank = 1
GROUP BY clientCaseId

Corey

I Has Returned!!

No, you're never too old to Yak'n'Roll if you're too young to die.

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources