Conditonal SUM function, or similar conditional aggregates

Are there any conditional aggregate functions, such as SUM()?

An example would probably be the best way to describe what I'm
trying to do...

I have the following table, named Orders, with the following records:

ItemNo qty_ord paid
-- ---- --
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 199.00
T101B 1 0.00
T101B 1 0.00
T101B 1 0.00
T101B 1 0.00
Z200L 1 50.00
Z200L 2 100.00

I want to produce the following result set:

ItemNo qty_gross qty_net
-- ---- ---
T101B 11 7
Z200L 3 3

The "qty_gross" column in the result set is the sum of
total items ordered within the ItemNo grouping.
Easy enough. However, I also want a column "qty_net" that
is the sum of qty_ord but ONLY IF the amount in the
"paid" column is > 0.

I tried using the HAVING clause, but that produces a
catch 22 situation. If I say "HAVING paid > 0" then
the qty_gross column is wrong because it leaves out rows
that contain records with paid = 0 values. If I leave
out the HAVING clause, then the "qty_net" is wrong.

Any ideas?

select ItemNo, Sum(qty_ord) as qty_gross, Sum(qty_ord) as qty_net
from Orders
group by qty_ord, paid, ItemNo
having paid > 0 ???

Thanks,
RobbieOn 15 Feb 2005 06:17:24 -0800, RobbieGotNeeds@.netscape.net wrote:

>Are there any conditional aggregate functions, such as SUM()?
(snip)

Hi Robbie,

No. But you can use any expression in an aggregate function, including the
conditional CASE expression.

>An example would probably be the best way to describe what I'm
>trying to do...
(snip)
>I have the following table, named Orders, with the following records:
>ItemNo qty_ord paid
>-- ---- --
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 199.00
>T101B 1 0.00
>T101B 1 0.00
>T101B 1 0.00
>T101B 1 0.00
>Z200L 1 50.00
>Z200L 2 100.00
>
>I want to produce the following result set:
>ItemNo qty_gross qty_net
>-- ---- ---
>T101B 11 7
>Z200L 3 3

SELECT ItemNo,
SUM(qty_ord) AS qty_gross,
SUM(CASE WHEN paid > 0 THEN qty_ord ELSE 0 END) AS qty_net
FROM Orders
GROUP BY ItemNo

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)