Try this:
SELECT custnumb,
SUM(CASE WHEN ordtype='A' then amount ELSE 0 END),
SUM(CASE WHEN ordtype='B' then quantity ELSE 0 END)
FROM <file>
WHERE <date range or other conditions>
GROUP BY custnumb
That should total the amount from records with an ordtype of "A", and the
quantity from records with an ordtype of "B".
Have fun!
Richard
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[
mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Tomasz Skorża
Sent: Tuesday, May 22, 2007 9:28 AM
To: midrange-l@xxxxxxxxxxxx
Subject: How to create this kind of SQL SELECT statement.
Hi
I would like to create a SELECT with diffrent criteria for fields in one
file.
I have a file with fields amount and quantity and additional fields
custnumb, ordernum, ordertype, orderdate etc.
The problem is that data for (sum of) amount and (sum of) quantity are
stored in diffrent rows. What is more complicated when row is "amount" in
field "quantity" value of weight is stored.
Next example
custnumb ordnumb ordtype orderdate amount quantity
1 1 A something 10 10
1 2 A something 20 20
1 1 B something 0 10
2 4 A something 10 15
2 4 B something 0 20
2 5 B something 0 20
And on report I would like to see (for same date range)
Custnumb amount quantity
1 30 10
2 10 40
Amount is sum of "amount" from rows A, quantity is sum of "quantity" from
rows B.
But how to write it? I created many joins but without success.
Have you any idea?
Regards
Tomasz Skorza
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.467 / Virus Database: 269.7.6/814 - Release Date: 5/21/2007
2:01 PM
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.467 / Virus Database: 269.7.6/814 - Release Date: 5/21/2007
2:01 PM
midrange.com
