|
View previous topic :: View next topic
|
| Author |
Message |
a b c d
New User
Joined: 04 Mar 2010
Posts: 13
Location: UK
|
|
|
|
Hi All,
My DB2 table looks as follows
| Code: |
Table T1
Col A Col B Col C
aaa S1 100
bbb S3 0
bbb 0
ccc S2 50
ddd P1 50
ddd 0 |
I need to report only those record where 1. Sum of the col C must be less than 100 or 2. Col B are spaces
Note - 1. For Sum of col C, I need to consider all records where col A are same. i.e for aaa sum=100, bbb=(0+0) = 0, ccc=50, ddd=(50+0)=50
2 - For Col B = spaces, I have to consider either off must be blank
In the above table except aaa record all other must be picked up |
|
| Back to top |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Yes, and ................. is there a question in here somewhere or is this just a hope that you get a free solution.
Please show what you have already tried and the problems arising.
Also, learn to use the code tags as shown below
| Code: |
[code] your
code
goes
here [/code] |
|
|
| Back to top |
|
|
a b c d
New User
Joined: 04 Mar 2010
Posts: 13
Location: UK
|
|
|
|
I tried with group by with no luck.....
I am looking for a query for the above scenario |
|
| Back to top |
|
|
a b c d
New User
Joined: 04 Mar 2010
Posts: 13
Location: UK
|
|
|
|
Hello Moderator,
How to put the code in code tag????????? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Here is an example from a db where the order_no has multiple line items,
each having a different prodkey. (I used 0, since prodkey is an integer)
to act like your column B = space.
price is amout, and i chose 6000 because that was convenient for my testing.
| Code: |
SELECT ORDER_NO
,sum(PRICE)
FROM AROMA.LINE_ITEMS P
WHERE EXISTS (SELECT ORDER_NO
FROM AROMA.LINE_ITEMS S
WHERE P.ORDER_NO = S.ORDER_NO
AND S.PRODKEY = 0)
group by order_no HAVING SUM(PRICE) < 6000;
|
the CODE part of BBcode is explained here |
|
| Back to top |
|
|
a b c d
New User
Joined: 04 Mar 2010
Posts: 13
Location: UK
|
|
|
|
| Thanks a lot Dick..... |
|
| Back to top |
|
|
|
|
