Re: Special SUM Statement
"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.03.29.16.14.49.992806.1445@bloomberg.net>...
> On Fri, 26 Mar 2004 05:11:09 -0500, Nicolas Mainczyk wrote:
>
> > 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.
>
> 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
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
;