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)
No comments:
Post a Comment