470,619 Members | 1,841 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,619 developers. It's quick & easy.

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,
Robbie

Jul 23 '05 #1
1 12182
On 15 Feb 2005 06:17:24 -0800, Ro************@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)
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Adal Chiriliuc | last post: by
64 posts views Thread by Morgan Cheng | last post: by
3 posts views Thread by Lyners | last post: by
1 post views Thread by anoj | last post: by
reply views Thread by Jack Black | last post: by
4 posts views Thread by B. Williams | last post: by
1 post views Thread by Peter Parker | last post: by
6 posts views Thread by GarryJones | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.