Home » SQL & PL/SQL » SQL & PL/SQL » Help me to correct this query (oracle 8i)
Help me to correct this query [message #423628] Fri, 25 September 2009 11:26 Go to next message
rajesh4851
Messages: 89
Registered: January 2007
Member
I want to find the voucher_product_id for a voucher , steps:

1. Find the entries in voucher_details table for the current values of voucher_id , voucher_version and voucher_invoice_no.
2. Sum the voucher_amount field by voucher_product_id.
3. get the voucher_product_id which has the largest total amount

Im using the below query to get the voucher_product_id :

   SELECT voucher_product_id
          ,SUM(voucher_amount) tot_paid
    FROM   voucher_details
    WHERE  voucher_id      = 'MG090812419007'  
    AND    voucher_version = 0
    AND    voucher_invoice_no    = 1
    GROUP BY voucher_product_id
    ORDER BY  tot_paid DESC
    ;

Expected result is 3664;
This is working for case 1 in the attachment. (data attached in the file) because i am taking order by tot_paid desc;
(ofcourse i am getting more than 1 records with this query, but i am taking the first record only, because i am sorting it in desc ).
In "case 1" the amount is different for line1 and 2 , hence it is giving the correct product_id.

In "case 2" the above query is not giving correct product_id value, since the amounts are same.

can you help me to solve this issue? If you feel this query is not correct, please give me some idea how to correct it.
  • Attachment: data.JPG
    (Size: 87.51KB, Downloaded 102 times)

[Updated on: Fri, 25 September 2009 11:27]

Report message to a moderator

Re: Help me to correct this query [message #423633 is a reply to message #423628] Fri, 25 September 2009 12:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Help me to correct this query [message #423634 is a reply to message #423633] Fri, 25 September 2009 13:28 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have the feeling you could use the analytical rank() somehow, though, but it's hard to tell without the actual data.

Also, on :

Quote:

3. get the voucher_product_id which has the largest total amount


So what should be returned if there are two voucher_product_ids which have the same largest total amount?
Re: Help me to correct this query [message #423648 is a reply to message #423628] Sat, 26 September 2009 00:00 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>select * from t1;

      COL1       COL2
---------- ----------
         1       1000
         1       2000
         2       1000
         2       2000

4 rows selected.

SQL>select col1, sum(col2) sm
  2  from t1
  3  group by col1;

      COL1         SM
---------- ----------
         1       3000
         2       3000

2 rows selected.

SQL>with temp as
  2  (select col1, sum(col2) sm
  3   from t1
  4   group by col1)
  5  select col1, sm from temp where rownum = 1;

      COL1         SM
---------- ----------
         1       3000

1 row selected.


regards,
Delna
Re: Help me to correct this query [message #424050 is a reply to message #423648] Wed, 30 September 2009 01:46 Go to previous message
rajesh4851
Messages: 89
Registered: January 2007
Member
Thanks for your responses. I have corrected the query.

SELECT voucher_product_id
          ,SUM(voucher_amount) tot_paid
    FROM   voucher_details
    WHERE  voucher_id      = 'MG090812419007'  
    AND    voucher_version = 0
    AND    voucher_invoice_no    = 1
    GROUP BY voucher_product_id
    ORDER BY  tot_paid DESC,
    voucher_product_id DESC  --added this extra condition
    ;
Previous Topic: Query with CONNECT BY PRIOR
Next Topic: not to allow Special character thru insert statement
Goto Forum:
  


Current Time: Tue Sep 27 14:32:00 CDT 2016

Total time taken to generate the page: 0.06654 seconds