RE: sql sum and case -- MIDRANGE-L

Thanks for all the help.
I have it working. I used Birgitta's method as it really helped me clean the SQL statement up. It was quite a bit larger than what I posted.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Tuesday, December 16, 2014 11:04 AM
To: 'Midrange Systems Technical Discussion'
Subject: AW: sql sum and case

I assume you want to have an additional column where material is neither 1 nor 2 nor 3.
If so I'd change the SQL Statement as follows:

select sum(case when Material = 1 then measuredlength end) as 'P2_1' ,
sum(case when Material = 2 then measuredlength end) as 'P2_2' ,
sum(case when Material = 3 Then measuredlength end) as 'P2_3' ,
Sum(Case When Material not in (1, 2, 3) Then measuredlength End as 'unknown'
FROM MAINSVW
Where MainType <> Transmission
and Diameter <= 2
and (InServiceDate < '2014-11-01' or InServiceDate is NULL)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Smith, Mike
Gesendet: Tuesday, 16.12 2014 16:03
An: midrange-l@xxxxxxxxxxxx
Betreff: sql sum and case

I've using a sql case statement to sum up measurements.
This is working fine, except now I would like to add an ELSE clause and I can seem to get it to work.

As a note(in case it matters) The below code is copied from SQL Server where I'm testing the original sql statement, but in actuality the code will be run from RPG using Scott's JDBCR4

Below is an excerpt from the sql statement that works.

select
sum(case
when maintype <> 'Transmission '
and Diameter <= 2 and Material = 1
and (inServiceDate < '2014-11-01' OR InServiceDate IS Null)
then measuredlength end) as
'P2_1' ,
sum(case
when maintype <> 'Transmission '
and Diameter <= 2 and Material = 2
and (inServiceDate < '2014-11-01' OR InServiceDate IS Null)
then measuredlength end) as
'P2_2' ,
sum(case
when maintype <> 'Transmission '
and Diameter <= 2 and Material = 3
and (inServiceDate < '2014-11-01' OR InServiceDate IS Null)
then measuredlength end) as
'P2_3'
FROM MAINSVW

I want something like this, but can't make it work.

select
sum(case
when maintype <> 'Transmission '
and Diameter <= 2 and Material = 1
and (inServiceDate < '2014-11-01' OR InServiceDate IS Null)
then measuredlength end) as
'P2_1' ,
sum(case
when maintype <> 'Transmission '
and Diameter <= 2 and Material = 2
and (inServiceDate < '2014-11-01' OR InServiceDate IS Null)
then measuredlength end) as
'P2_2' ,
sum(case
when maintype <> 'Transmission '
and Diameter <= 2 and Material = 3
and (inServiceDate < '2014-11-01' OR InServiceDate IS Null)
then measuredlength end) as
'P2_3'

ELSE
sum measuredlength as 'unknown'

FROM MAINSVW

Thanks

Mike
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.