1. Registered User
Join Date
Feb 2009
Posts
38

I'll try to explain better this time. Hope this is the correct way:

I have two tables
EP with code_d,total_amount and values:

code_d,total_amount
1, 30000
3, 10000
6, 4000
3, 6000
6, 3000
2, 300000
4, 600000
5, 400000

and table DP with code_d,amount and values:
code_d,amount
1, 20000
3, 6000
6, 1000
1, 8000
3, 4500
6, 1000
2, 1000
3, 3000
6, 1500
3, 2500
6, 1500
5, 2000
2, 100000
4, 200000
5, 250000
2, 100000
4, 100000
5, 200000
6, 10000
and i must find the codes(d) in which the sum (amount) for each code has overpassed the sum (total amount) for each code.

eg. in total amounts code_d=6 has total value 7000 but in amount code_d=6 has total value 15000.

so i need this:
code_d
5
6

I DID THIS, but says NO DATA FOUND. SO thats not correct.

SELECT EP.CODE_D
FROM EP
WHERE ((SELECT SUM(EP.TOTAL_AMOUNT) FROM EP JOIN DP
ON EP.CODE_D=DP.CODE_D)<(SELECT SUM(DP_AMOUNT)FROM EP JOIN DP
ON EP.CODE_D=DP.CODE_D))

Some help pls..Thank you
Go to Top of Page
Last edited by misty1976; 02-15-09 at 05:19.

2. Registered User
Join Date
Mar 2007
Posts
629
Code:
```SELECT EP.CODE_D
FROM EP
WHERE ((SELECT SUM(EP.TOTAL_AMOUNT) FROM EP JOIN DP
ON EP.CODE_D=DP.CODE_D)<(SELECT SUM(DP_AMOUNT)FROM EP JOIN DP
ON EP.CODE_D=DP.CODE_D))```
As the subqueries in WHERE clause contain both joined table, they are not dependent on rows from table EP in the main query. They return overall sums of the defined amounts.
Remove the reference to EP table from subqueries (so the join condition will belong to the WHERE clause). EP values than will be taken from the rows in the main SELECT.

3. Registered User
Join Date
Jan 2009
Location
Posts
51
Try this

SELECT * FROM ( SELECT EP.CODE_D
, SUM (EP.TOTAL_AMOUNT) ep_ammount
, SUM (DP_AMOUNT) dp_ammount
FROM EP , DP
WHERE EP.CODE_D=DP.CODE_D
GROUP BY EP.CODE_D ) x
WHERE x.dp_ammount > x.ep_ammount

4. Registered User
Join Date
Feb 2009
Posts
38
Thank you

5. Registered User
Join Date
Jan 2009
Posts
17
hi,
try this once

select e.code_d from
(
select code_d,sum(total_amount) tamt from ep group by code_d)e,
(select code_d,sum(dp_amount) amt from dp group by code_d)d
where e.tamt < d.amt
and e.code_d=d.code_d

OUTPUT---
-----------------------

CODE_D
----------
5
6

2 rows selected.

6. Registered User
Join Date
Feb 2009
Posts
38
Originally Posted by sridhar.dbe
hi,
try this once

select e.code_d from
(
select code_d,sum(total_amount) tamt from ep group by code_d)e,
(select code_d,sum(dp_amount) amt from dp group by code_d)d
where e.tamt < d.amt
and e.code_d=d.code_d

OUTPUT---
-----------------------

CODE_D
----------
5
6

2 rows selected.
Thanks a lot

7. Registered User
Join Date
Feb 2009
Posts
38
thank you all
Last edited by misty1976; 02-16-09 at 06:06.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•