--- In firebird-support@yahoogroups.com, "ezzodder@..." <ezzodder@...> wrote:

>
> I've been using MySQL for years and I'm trying FB 2.5 to see how it
> goes.
>
> I'm stuck on creating the equivalent SQL in Firebird.
>
> In MySQL I'd use:
>
> SELECT SUM(IF(Amt>0,1,0)) AS Num_Pos, SUM(IF(Amt>0,Amt,0)) AS Pos_Owing,
> SUM(IF(Amt<0,1,0)) AS Num_Neg, SUM(IF(Amt<0,Amt,0)) AS Neg_Owing FROM
> TABLE1;
>
> So I'd like to have 4 columns, the number of rows where Amt is positive
> and the sum of the positive Amt, and the number of rows where Amt is
> negative and the sum of the negative Amt.
>
> I can't seem to get FB to do this in one SQL statement. Can someone
> help?

You can use a standard CASE statement for this:

SELECT SUM(case
when Amt > 0 then
else null
end) AS Num_Pos,
SUM(case
when Amt > 0 then Amt
else null
end) AS Pos_Owing,
SUM(case
when Amt < 0 then 1
else null
end) AS Num_Neg,
SUM(case
when Amt < 0 then Amt
else null
end) AS Neg_Owing
FROM table1