Problem Group by command [message #309607] |
Fri, 28 March 2008 02:33  |
sinsay
Messages: 4 Registered: March 2008 Location: ...
|
Junior Member |
|
|
Hi.
I have a problem when I used Group by function.
I want to Summary amount and quantity for each PO (1 PO has many Price)
With below data is my sql command.
create or replace view SALE_PDS as
select CUSTOMER_ID CUSTOMER,
PLACE PLACE,
DELI_DATE DELIVERY_DATE,
sum(QTY) QTY,
round(sum(QTY)*PRICE,2) AMOUNT,
substr(PO,16) ORDERNO1,
substr(PO,1,14) ORDERNO2,
BIN MAIL,
SITE SITE
from SALE_LINE
group by CUSTOMER_ID,
PLACE,
DELI_DATE,
PRICE,
PO,
BIN,
SITE
with read only
I want to calculate Amount by Qty*Price and use group by function I must add field Price into group by condition 'cause don't add it will be return error msg " ORA-00979: not a GROUP BY expression", but when I add field Price into group by condition result is incorrect, same OrderNo1 is not merge please see below data.
DELIVERY_DATE ORDERNO1 QTY AMOUNT
01/03/2008 2008030111 3 4863.24
01/03/2008 2008030111 15 5747.4
01/03/2008 2008030109 20 2221.6
Correctly result must be this.
|
V
DELIVERY_DATE ORDERNO1 QTY AMOUNT
01/03/2008 2008030111 18 10610.64
01/03/2008 2008030109 20 2221.6
How can I do?
Help me please!!! T-T
Thanks you.
|
|
|
|
|
|
Re: Problem Group by command [message #309617 is a reply to message #309615] |
Fri, 28 March 2008 03:05   |
sinsay
Messages: 4 Registered: March 2008 Location: ...
|
Junior Member |
|
|
Sorry, if it make you puzzle I was posted sample of result only.
This my fully result from my code.
1 TMTS S1 01/03/2008 3 4863.24 2008030111 1STGT1CS1D495 16 32
2 TMTS S1 01/03/2008 15 5747.4 2008030111 1STGT1CS1D495 16 32
3 TMTS S1 01/03/2008 20 2221.6 2008030109 1STGT1CS1D494 14 32
Did you want to told me like this?
Regards
|
|
|
|
|
Re: Problem Group by command [message #309629 is a reply to message #309607] |
Fri, 28 March 2008 03:54   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Just a guess: if DELI_DATE has DATE or TIMESTAMP type, it possibly contains different time parts.
You may check it by replacing DELI_DATE with TO_CHAR( DELI_DATE, 'DD/MM/YYYY HH24:MI:SS' ) in your query.
[Edit: corrected mistake in function name]
[Updated on: Fri, 28 March 2008 03:55] Report message to a moderator
|
|
|
|
Re: Problem Group by command [message #309665 is a reply to message #309607] |
Fri, 28 March 2008 06:07   |
varu123
Messages: 754 Registered: October 2007
|
Senior Member |
|
|
1 TMTS S1 01/03/2008 3 4863.24 2008030111 1STGT1CS1D495 16 32
2 TMTS S1 01/03/2008 15 5747.4 2008030111 1STGT1CS1D495 16 32
3 TMTS S1 01/03/2008 20 2221.6 2008030109 1STGT1CS1D494 1432
YOu have nine columns in select statement and 10 columns in the output above?
The customer ID is different.How can you group by?
[Updated on: Fri, 28 March 2008 06:09] Report message to a moderator
|
|
|
Re: Problem Group by command [message #309726 is a reply to message #309665] |
Fri, 28 March 2008 10:33   |
Tafer
Messages: 64 Registered: July 2005 Location: Here!
|
Member |
|
|
Instead of:
round(sum(QTY)*PRICE,2) AMOUNT,
Try with this:
sum(round(QTY*PRICE,2)) AMOUNT,
You should be able to remove the PRICE from the group by.
Hope it helps.
|
|
|
|
Re: Problem Group by command [message #309738 is a reply to message #309731] |
Fri, 28 March 2008 11:20   |
skooman
Messages: 913 Registered: March 2005 Location: Netherlands
|
Senior Member |
|
|
The round shouldn't be inside the sum of course, however, getting the price between the brackets of sum does make sense to me. From an sql point of view: if price is not "inside" the sum, then it should be in the group be (hence the error OP reported). From a functional point of view: of course you want to multiply the quantity by the price for 1 row, it makes no sense at all to first add up the quantities and then multiply that by the price.
An example:
id customer quantity price
1 Smith 10 3
2 Smith 5 8
3 Jones 10 12
Result should be:
Smith (10*3) + (5*8) = 30+40 = 70
Jones (10*12) = 120
So, in SQL:
select customer
sum(quantity*price)
from <sometable>
group by customer
But probably I completely misunderstand the issue, since a whole bunch of people is already wandering around this...
|
|
|
Re: Problem Group by command [message #309740 is a reply to message #309738] |
Fri, 28 March 2008 11:33   |
Tafer
Messages: 64 Registered: July 2005 Location: Here!
|
Member |
|
|
skooman wrote on Fri, 28 March 2008 11:20 | The round shouldn't be inside the sum of course, however, getting the price between the brackets of sum does make sense to me. From an sql point of view: if price is not "inside" the sum, then it should be in the group be (hence the error OP reported). From a functional point of view: of course you want to multiply the quantity by the price for 1 row, it makes no sense at all to first add up the quantities and then multiply that by the price.
An example:
id customer quantity price
1 Smith 10 3
2 Smith 5 8
3 Jones 10 12
Result should be:
Smith (10*3) + (5*8) = 30+40 = 70
Jones (10*12) = 120
So, in SQL:
select customer
sum(quantity*price)
from <sometable>
group by customer
But probably I completely misunderstand the issue, since a whole bunch of people is already wandering around this...
|
Exactly.
Heh... about the round inside the sum: big mistake (I did it on a hurry).
|
|
|
|
|
Re: Problem Group by command [message #309750 is a reply to message #309748] |
Fri, 28 March 2008 12:16  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | First answer gets the cookie, and the idea is there!
|
I understand now why you post so many wrong answers.
In the end, till OP doesn't answer the questions I posted no one can answer as the solution depends on the response of these questions.
Regards
Michel
|
|
|