Results 1 to 11 of 11

Thread: select problem

  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: select problem

    hie all im trying to select sum and count from two tables but the script i am using is returning something else

    im using this script:
    Code:
    select sum(a.reload_amount_f1) sum_f1,sum(b.reload_amount_f2) sum_f2,
    count(a.reload_amount_f1)count_f1,count(b.reload_amount_f2)count_f2 
    from trap_ra_ers_balance a, trap_ra_ppb_reload b
    where (TO_DATE(a.reload_date,'DD-MM-YY')  >=  '01-JAN-06'
    AND TO_DATE(a.reload_date,'DD-MM-YY')  <=  '01-JAN-06')
    and a.dealer_id = b.dealer_id(+)
    group by a.dealer_id
    but it is returning a very large number for a particular dealer

    for example i took a dealer id and ran this script and it gives me the correct amount
    Code:
    select nvl(sum(reload_amount_f2),0) sum_f2, count(reload_amount_f2) count_f2
    from trap_ra_ppb_reload
    where (TO_DATE(reload_date,'DD-MM-YY')  >=  '01-JAN-06'
    AND TO_DATE(reload_date,'DD-MM-YY')  <=  '01-JAN-06')
    and dealer_id = 'D0109-KS001'
    and also thos script for the other table
    Code:
    select sum(reload_amount_f1) sum_f1, count(reload_amount_f1) count_f1
    from trap_ra_ers_balance
    where (TO_DATE(reload_date,'DD-MM-YY')  >=  '01-JAN-06'
    AND TO_DATE(reload_date,'DD-MM-YY')  <=  '01-JAN-06')
    and dealer_id = 'D0109-KS001'
    how do i rectify this ?? how to write the script that will combine and return the wanted result
    have attached the result..

    thanks a lot
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    First of all, if

    reload_date >= 01. jan 06 AND reload_date <= 01. jan 06

    it should be simplified to:
    a) reload_date = 01. jan 06 (if you really need only one date), or
    b) reload_date BETWEEN 01. jan 06 AND 15. jan 06 (if those dates are different).

    Secondly, why does query upon 'trap_ra_ppb_reload' have a NVL function on 'reload_amount_f2' column, and the other one doesn't? Was it intentional, or did you (perhaps) not notice that?

    One possible way to get the correct result would be to union two queries that provide good output. Simplified (perhaps TOO simplified) query that will, though, produce two output lines instead of one, would then be:
    Code:
    SELECT SUM (reload_amount_f1) sum_f1, 
           COUNT (reload_amount_f1) count_f1
      FROM trap_ra_ers_balance
     WHERE TO_DATE (reload_date, 'DD-MM-YY') = '01-JAN-06'
       AND dealer_id = 'D0109-KS001'
    UNION
    SELECT NVL (SUM (reload_amount_f2), 0) sum_f2,
           COUNT (reload_amount_f2) count_f2
      FROM trap_ra_ppb_reload
     WHERE TO_DATE (reload_date, 'DD-MM-YY') = '01-JAN-06'
       AND dealer_id = 'D0109-KS001';
    But, the question is: was this the correct approach? I'd rather use an inline view and rewrite the query the way it really should look like:
    Code:
    SELECT   a.dealer_id, 
             SUM   (a.reload_amount_f1) sum_f1,
             COUNT (a.reload_amount_f1) count_f1, 
             SUM   (b.reload_amount_f2) sum_f2,
             COUNT (b.reload_amount_f2) count_f2
        FROM trap_ra_ers_balance a,
             (SELECT SUM   (reload_amount_f2) sum_f2,
                     COUNT (reload_amount_f2) count_f2
                FROM trap_ra_ppb_reload
             ) b
       WHERE a.dealer_id = b.dealer_id
    GROUP BY a.dealer_id;
    Now, if I didn't make a mistake somewhere, I hope this should give a correct result.

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    hie little foot,
    first of all, thank for the explanation.. it was useful..

    but to answer ur questions
    1) the nvl function is intentionally done
    2) i knew about the union and it didnt return what i want

    i tried the second script
    Code:
    SELECT   a.dealer_id,
             SUM   (a.reload_amount_f1) sum_f1,
             COUNT (a.reload_amount_f1) count_f1, 
             SUM   (sum_f2) sum_f2,
             COUNT (count_f2) count_f2
        FROM trap_ra_ers_balance a,
             (SELECT SUM   (reload_amount_f2) sum_f2,
                     COUNT (reload_amount_f2) count_f2
               FROM trap_ra_ppb_reload
             ) b		 
    WHERE a.dealer_id = 'D0109-KS001'
    AND to_char(reload_date, 'dd/mm/yyyy') = '01/01/2006'
    GROUP BY a.dealer_id;
    but then it is returning correct for the f1 columns but not the f2 columns
    any help here ? have also attached the results..
    Attached Files Attached Files
    Last edited by shatishr; 01-16-06 at 00:06.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, YOUR second script is not same as mine. There's no join between (aliases) "a" and "b"; perhaps you could try with

    WHERE a.dealer_id = 'D0109-KS001'
    AND a.dealer_id = b.dealer_id
    AND to_char(reload_date, 'dd/mm/yyyy') = '01/01/2006'
    GROUP BY a.dealer_id;

  5. #5
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    nope liitle foot,
    its still giving the correct amount for f1 and returning the wrong for the f2 column...

    any other ways anyone knows off ??

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Hm, no wonder it doesn't work ... If it's not a problem, would you try this one?
    Code:
    SELECT   a.dealer_id, 
             SUM   (a.reload_amount_f1) sum_f1,
             COUNT (a.reload_amount_f1) count_f1, 
             sum_f2,
             count_f2
        FROM trap_ra_ers_balance a,
             (SELECT dealer_id,
                     SUM   (reload_amount_f2) sum_f2,
                     COUNT (reload_amount_f2) count_f2
                FROM trap_ra_ppb_reload
                GROUP BY dealer_id
             ) b
       WHERE a.dealer_id = b.dealer_id
    GROUP BY a.dealer_id, sum_f2, count_f2;

  7. #7
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Red face

    hie littlefoot.. im afraid the above script doesnt work
    nvm let me give another picture

    lets say t1 contains
    reload_date, dealer_id, reload_amount_1
    1/1/06, 001, 10
    1/1/06, 001, 15
    2/1/06, 003, 10

    t2 contains
    reload_date, dealer_id, reload_amount_2
    1/1/06, 001, 10
    1/1/06, 001, 15
    2/1/06, 003, 10
    3/1/06, 003, 10

    the reporting table should look like
    reload_date, dealer_id, reload_amount_1, reload_count_1, reload_amount_2, reload_count_2
    1/1/06, 001, 25, 2, 25, 2
    2/1/06, 003, 10, 1, 10, 1
    3/1/06, 003, 0, 0, 10, 1


    thats about it, if i can get a script to get the above then my problem is solved

    any helpppp.....

  8. #8
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    littlefoot,
    refering to the above or below quote,
    what i have done is combined t1 and t1 into a new table with columns
    reload_date, dealer_id, reload_amount, reload_count, source
    and ive group by the dealer id and reload date and also do the sum and count..

    later i have another table and i run this script

    Code:
    (select DISTINCT trunc(reload_date) reload_date, dealer_id,
    	(select reload_amount
    	from TEST a
    	where source = 'PPB'
    	and a.reload_date = b.reload_date
    	and a.dealer_id = b.dealer_id
    	) as reload_amount_ppb,
    	(select reload_count
    	from TEST a
    	where source = 'PPB'
    	and a.reload_date = b.reload_date
    	and a.dealer_id = b.dealer_id
    	) as reload_count_ppb,
    	(select reload_amount
    	from TEST a
    	where source = 'ERS'
    	and a.reload_date = b.reload_date
    	and a.dealer_id = b.dealer_id
    	) as reload_amount_ers,
    	(select reload_count
    	from TEST a
    	where source = 'ERS'
    	and a.reload_date = b.reload_date
    	and a.dealer_id = b.dealer_id
    	) as reload_count_ers
    from TEST b)
    i am getting the correct result but then it takes a long time to process and would you mind explain to me how does the script im using works as in the 'a' and 'b' for the same table??
    need your expertise here...



    Quote Originally Posted by shatishr
    hie littlefoot.. im afraid the above script doesnt work
    nvm let me give another picture

    lets say t1 contains
    reload_date, dealer_id, reload_amount_1
    1/1/06, 001, 10
    1/1/06, 001, 15
    2/1/06, 003, 10

    t2 contains
    reload_date, dealer_id, reload_amount_2
    1/1/06, 001, 10
    1/1/06, 001, 15
    2/1/06, 003, 10
    3/1/06, 003, 10

    the reporting table should look like
    reload_date, dealer_id, reload_amount_1, reload_count_1, reload_amount_2, reload_count_2
    1/1/06, 001, 25, 2, 25, 2
    2/1/06, 003, 10, 1, 10, 1
    3/1/06, 003, 0, 0, 10, 1


    thats about it, if i can get a script to get the above then my problem is solved

    any helpppp.....
    Last edited by shatishr; 01-18-06 at 03:03.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try

    Code:
    SELECT   a.dealer_id, 
             sum_f1,
             count_f1, 
             sum_f2,
             count_f2
        FROM trap_ra_ers_balance a,
             (SELECT dealer_id,
                     SUM   (reload_amount_f2) sum_f2,
                     COUNT (reload_amount_f2) count_f2
                FROM trap_ra_ppb_reload
                GROUP BY dealer_id
             ) b
             (SELECT dealer_id,
                     SUM   (reload_amount_f1) sum_f1,
                     COUNT (reload_amount_f1) count_f1
                FROM trap_ra_ers_balance
                where TO_DATE (reload_date, 'DD-MM-YY') = '01-JAN-06'
                GROUP BY dealer_id
             ) a
       WHERE a.dealer_id = b.dealer_id(+);
    Last edited by beilstwh; 01-18-06 at 09:54.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    as a small addition, instead of

    Code:
    where TO_DATE (reload_date, 'DD-MM-YY') = '01-JAN-06'
    use

    Code:
    where reload_date between to_date('01/01/2006 00:00:00', 'MM-DD-YYYY HH24:MI:SS') and to_date('01/01/2006 23:59:59', 'MM-DD-YYYY HH24:MI:SS')
    If there's an index on reload_date, then this might speed up the query. If there's no index on reload_date, at least this prepares you for it in the future if one is applied.

    -cf

  11. #11
    Join Date
    Jan 2006
    Posts
    1

    Try this....

    select RELOAD_DATE, DEALER_ID, nvl(sum(RELOAD_AMOUNT_1), 0), count(RELOAD_AMOUNT_1), nvl(sum(RELOAD_AMOUNT_2),0), count(RELOAD_AMOUNT_2)
    from
    (select t1.RELOAD_DATE, t1.DEALER_ID, t1.RELOAD_AMOUNT_1, to_number(NULL) RELOAD_AMOUNT_2 from t1
    union all
    select t2.RELOAD_DATE, t2.DEALER_ID, to_number(NULL), t2.RELOAD_AMOUNT_2 from t2)
    group by RELOAD_DATE, DEALER_ID

    HTH
    IanC

Posting Permissions

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