Special SUM Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nicolas Mainczyk - 26 Mar 2004 11:11 GMT

Hi,

What is the equivalent statement for sum(if(condition,result1,result2)) for
mysql RDBMS or CASE WHEN ... for sybase or DECODE for Oracle ?
I have a statement like SELECT sum(field)/sum(items) and I got an error when
sum(items)=0 (div/0 error) so I'd like to use if sum(items)=0 display 0 or
NULL otherwise calculate the expression.

TIA,
Nicky.

Reply to this Message

Art S. Kagel - 29 Mar 2004 22:14 GMT

> Hi,
>
[quoted text clipped - 3 lines]
> sum(items)=0 (div/0 error) so I'd like to use if sum(items)=0 display 0 or
> NULL otherwise calculate the expression.

IDS (7.30/9.20 and later) has both CASE and DECODE, RTFineM. This may also
be a job for a pure SQL HAVING clause.

SELECT key, sum(field)/sum(items)
FROM ...
WHERE ...
GROUP BY key
HAVING sum(item) > 0;

Art S. Kagel

Reply to this Message

Curtis Crowson - 31 Mar 2004 21:33 GMT

> > Hi,
> >
[quoted text clipped - 14 lines]
>
> Art S. Kagel

having sum(item) <> 0; ;-)

select
key,
case of
when sum(items) != 0 then sum(field)/sum(items)
else null
end
from ...
where ...
group by key
;

Reply to this Message