SUM(Case ) Statement not adding

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)

SUM(Case ) Statement not adding

Author

Topic

Ditrex
Starting Member

14 Posts

Posted - 2007-09-27 : 14:26:11

Hey guys I have a SUM(Case) statement that's not adding. If you can point me in the right direction that would be great. Here is what I have.

Select Sum(case when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'A' Then 4.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'A-' Then 3.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B+' Then 3.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B' Then 3.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B-' Then 2.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C+' Then 2.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C' Then 2.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C-' Then 1.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D+' Then 1.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D' Then 1.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D-' Then .7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'F' Then 0.0 End )

All I get is the value of the letter grade but it doesn't add it. What I'm I doing wrong?

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 14:35:43

Need to see the whole SQL statement, not just part of it, please.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-27 : 14:50:01

Advice: create a table of LetterGrades and store the values in there. use your database to its full potential and store data in your tables, not embedded in case expressions in your SQL ...

see:

http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Ditrex
Starting Member

14 Posts

Posted - 2007-09-27 : 14:50:11

declare @t table (Term char(5), ID int, LastName varchar(50), FirstName varchar(50),
CRN int, Subject varchar(20), LetterGrade char(1))

insert into @t

select '20072', 1, 'Doe', 'John', 1234, 'CHEM', 'B'

union all select '20072', 1, 'Doe', 'John', 3214, 'BIOL', 'A'
union all select '20072', 1, 'Doe', 'John', 4321, 'LAW', 'B'
union all select '20072', 2, 'Bauer', 'Jack', 1234, 'CHEM', 'A'
union all select '20072', 2, 'Bauer', 'Jack', 3214, 'BIOL', 'C'
union all select '20072', 2, 'Bauer', 'Jack', 5467, 'FIN', 'B'

select t.Term, t.ID, t.LastName, t.FirstName, CRN, Subject, t.LetterGrade,

SUM(case when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'A' Then 4.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'A-' Then 3.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B+' Then 3.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B' Then 3.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B-' Then 2.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C+' Then 2.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C' Then 2.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C-' Then 1.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D+' Then 1.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D' Then 1.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D-' Then .7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'F' Then 0.0 End )

from @t t

Group By t.Term, t.ID, t.LastName, t.FirstName, CRN, Subject, t.LetterGrade

Ditrex
Starting Member

14 Posts

Posted - 2007-09-27 : 14:58:48

quote:
Originally posted by jsmith8858

Advice: create a table of LetterGrades and store the values in there. use your database to its full potential and store data in your tables, not embedded in case expressions in your SQL ...

see:

http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

I can't, I'm not allowed to write into this table, that's why I'm constricted to do it this way.

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 15:02:22

What's supposed to happen, for example, when the Subject = 'Law'?

(Thanks for the worked example, that's a huge help and saves us all having to manually make tables / data)

Kristen

Ditrex
Starting Member

14 Posts

Posted - 2007-09-27 : 15:09:34

What's suppose to happen is that it will only calculate the letter grade for those that have the subject "CHEM", "BIOL" and "PHYS". Then it should take that number value and add them. Right now it's just converting the value from A to 4.0, and B to 3.0, but it's not adding them. So in essence I wat to show for example 7.0

Ditrex
Starting Member

14 Posts

Posted - 2007-09-27 : 15:11:22

Just copy and paste the last code I posted, I'm using a Temporary table, so it will show you what I'm talking about.

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 15:20:28

"Just copy and paste the last code I posted, I'm using a Temporary table, so it will show you what I'm talking about."

Yup, I twigged that

Your sample data has no more than one combination of Subject Plus grade:

CHEM B
BIOL A
LAW B - Not applicable
CHEM A
BIOL C
FIN B - Not applicable

so there aren't enough values to aggregate more than one value per row

Kristen

Ditrex
Starting Member

14 Posts

Posted - 2007-09-27 : 15:25:40

There are two values that you can aggregate per ID.

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 15:27:23

OK, I'm being thick, which two please?

Ditrex
Starting Member

14 Posts

Posted - 2007-09-27 : 15:38:51

select '20072', 1, 'Doe', 'John', 1234, 'CHEM', 'B'

union all select '20072', 1, 'Doe', 'John', 3214, 'BIOL', 'A'
union all select '20072', 1, 'Doe', 'John', 4321, 'LAW', 'B'
union all select '20072', 2, 'Bauer', 'Jack', 1234, 'CHEM', 'A'
union all select '20072', 2, 'Bauer', 'Jack', 3214, 'BIOL', 'C'
union all select '20072', 2, 'Bauer', 'Jack', 5467, 'FIN', 'B'

JOHN DOE has an A in Chem and B in BIOL, Jack Bauer has an A in CHEM and C in BIOL. lol, the names are funny, I know!

Ditrex
Starting Member

14 Posts

Posted - 2007-09-27 : 15:41:17

Sweet! I got it. At the end of the Case statement, after the close parenthesis, all I had to do was add this Over(Partition by Term, ID)

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 15:41:54

But you are grouping on

Group By t.Term, t.ID, t.LastName, t.FirstName, CRN, Subject, t.LetterGrade

so the SUM will only aggregate where the name, the CRN, the Subject and the LetterGrade are the same.

I expect I'm missing something again, but that's how I read it.

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-27 : 16:22:40

None of this makes any sense ..... Where did the partition come from?

I think all he had to do was remove LetterGrade from the GROUP BY ....

- Jeff
http://weblogs.sqlteam.com/JeffS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-09-27 : 16:29:58

[code]select t.Term, t.ID, t.LastName, t.FirstName, CRN, Subject, t.LetterGrade,
(SELECT

SUM(case when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'A' Then 4.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'A-' Then 3.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B+' Then 3.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B' Then 3.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B-' Then 2.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C+' Then 2.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C' Then 2.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C-' Then 1.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D+' Then 1.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D' Then 1.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D-' Then .7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'F' Then 0.0 End )

FROM @T AS Sub
WHERE Sub.Term = t.Term
AND Sub.ID = t.ID
GROUP BY Sub.ID) AS Total

from @t t

Group By t.Term, t.ID, t.LastName, t.FirstName, CRN, Subject, t.LetterGrade
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 02:48:36

Drop the LETTERGRADE from the GROUPING! You are summing it up, right!?

select t.Term, t.ID, t.LastName, t.FirstName, CRN, Subject,
SUM(case when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'A' Then 4.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'A-' Then 3.7 
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B+' Then 3.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B' Then 3.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'B-' Then 2.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C+' Then 2.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C' Then 2.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'C-' Then 1.7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D+' Then 1.3
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D' Then 1.0
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'D-' Then .7
when Subject in ('CHEM', 'BIOL', 'PHYS') and LetterGrade = 'F' Then 0.0 End )

from @t t

Group By t.Term, t.ID, t.LastName, t.FirstName, CRN, Subject

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 03:41:06

Well, the way I read it the Subjects have to go too:

"JOHN DOE has an A in Chem and B in BIOL, Jack Bauer has an A in CHEM and C in BIOL"

But hey! the OP has fixed it, and we may never know how ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources