Help me to correct this query [message #423628] |
Fri, 25 September 2009 11:26  |
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 442 times)
[Updated on: Fri, 25 September 2009 11:27] Report message to a moderator
|
|
|
|
Re: Help me to correct this query [message #423634 is a reply to message #423633] |
Fri, 25 September 2009 13:28   |
ThomasG
Messages: 3212 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 #424050 is a reply to message #423648] |
Wed, 30 September 2009 01:46  |
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
;
|
|
|