Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    38

    Unanswered: sum different amounts

    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. #2
    Join Date
    Mar 2007
    Posts
    623
    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. #3
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    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
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  4. #4
    Join Date
    Feb 2009
    Posts
    38
    Thank you

  5. #5
    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. #6
    Join Date
    Feb 2009
    Posts
    38
    Quote 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. #7
    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
  •