conditional sum over several fields, without if?

[kittyyo]

Hi all!

I'm trying to sum up values from several columns, that way:

id | val1 | val2 | val3 | type1 | type2 | type3 |

val* are double columns, type* are integer columns.

I'd like to select the sum of all val_i columns where the value of the type_i column = 4, so for this expample row

id | val1 | val2 | val3 | type1 | type2 | type3
-----------------------------------------------
1 | 0.1 | 0.2 | 0.3 | 1 | 4 | 4

the resulting sum of val* columns would be 0.5, and the result set would be

id | sum
--------
1 | 0.5

I know this is a table structure problem (I didnt design the table), and please consider that I'm using Firebird and therefore do not have a function like IF().

Any clue how I could select this?

Thanks a lot,
Anne

 

[fredericofonseca]

select (case t1 when 4 then d1 else 0 end) +
(case t2 when 4 then d2 else 0 end) +
(case t3 when 4 then d3 else 0 end) as max1
from t1
where dx = valx and tx = typex

adjust according to your table name and output expectations.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[r937]

yeah, but frederico, how do you do it without CASE, which firebird does not support?



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[fredericofonseca]

as far as I know Firebird DOES support case statements.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[fredericofonseca]

to be more precise.

V1.0 did not support case or if statements

V 1.5 does support both.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[r937]

thanks

i've been all over the sourceforge and ibphoenix sites and they've done a marvellous job of hiding the documentation that might contain this information

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[fredericofonseca]

http://www.firebirdsql.com

may contain the information you need.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[r937]

http://firebirdsql.com

looks really really really similar to

http://firebird.sourceforge.net

if you have a direct url to where it explains the CASE statement, that'd be nice

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[fredericofonseca]

http://firebird.sourceforge.net/index.php?op=files&id=engine

and more specificaly

http://www.ibphoenix.com/downloads/Firebird_v15.108_ReleaseNotes.pdf

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[kittyyo]

Thank you all for the discussion! As I found out, Firebird does support CASE. No, I didn't find it in any documentation either, I tried it and it worked (FB 1.5).

The only firebird documentation I know is on the IBPhoenix developer CD... which one has to pay for :-(

Thanks again,
Anne