473,326 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 12343
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Adal Chiriliuc | last post by:
I think a function similar to the one below should be added to the builtin module: def boolselect(condition, trueresult, falseresult): if condition: return trueresult else: return falseresult...
64
by: Morgan Cheng | last post by:
Hi All, I was taught that argument valuse is not supposed to be changed in function body. Say, below code is not good. void foo1(int x) { x ++; printf("x+1 = %d\n", x); } It should be...
3
by: Lyners | last post by:
I am having a hard time with this one, and I thought it would be easy. I have a datagrid in which I have textboxs for users to enter data. One of the fields in the database behind the datagrid...
1
by: anoj | last post by:
Hi All i need to write a function in vb.net similar to pack() in PHP kindly help. Thanks Anoj Kumar
0
by: Jack Black | last post by:
Hi, all! Using VS2k3 under XP/Pro, latest everything... Just curious, but is there a method or function equivalent to ColdFusion's CFDUMP tag? Basically, feed it any variable type and it's...
4
by: B. Williams | last post by:
I have written this program for an assignment that requires a static member function to set a static data member, but I can't figure out how to get it to change the value once set. Would someone...
1
by: Peter Parker | last post by:
I understand for php5 there is function filter_var as in filter_var($var, FILTER_SANITIZE_URL);Is there similar function in php4.3? Thank you
6
by: GarryJones | last post by:
The following function checks to see if a variable read from a mysql database is numeric. The funtion worked until I hit the value 15 303 That is a valid number but because of the space between...
6
by: Benny the Guard | last post by:
I have a task. I have to create a query that has optional elements for a where clause. This code is needed in a few different places so I fuigured a function to create a cursor wouold be best. So...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.