Home » SQL & PL/SQL » SQL & PL/SQL » Problem Group by command
Problem Group by command [message #309607] Fri, 28 March 2008 02:33 Go to next message
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? Confused
Help me please!!! T-T Sad
Thanks you.
Re: Problem Group by command [message #309611 is a reply to message #309607] Fri, 28 March 2008 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you need what you posted, remove all other columns from your select list and group by clause.

Regards
Michel
Re: Problem Group by command [message #309613 is a reply to message #309611] Fri, 28 March 2008 02:52 Go to previous messageGo to next message
sinsay
Messages: 4
Registered: March 2008
Location: ...
Junior Member
็Hi,Michel Cadot.
Thanks for your kindly suggest.All columns in select list that I need to use and if I remove field Price from group by, It will return error msg " ORA-00979: not a GROUP BY expression"

Regards
Wi

[Updated on: Fri, 28 March 2008 02:56]

Report message to a moderator

Re: Problem Group by command [message #309615 is a reply to message #309613] Fri, 28 March 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are they or not in your result?
They are not in what you posted.

Regards
Michel
Re: Problem Group by command [message #309617 is a reply to message #309615] Fri, 28 March 2008 03:05 Go to previous messageGo to next message
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 #309625 is a reply to message #309617] Fri, 28 March 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the columns are?
And you want to sum and count what?

Regards
Michel
Re: Problem Group by command [message #309628 is a reply to message #309625] Fri, 28 March 2008 03:53 Go to previous messageGo to next message
sinsay
Messages: 4
Registered: March 2008
Location: ...
Junior Member
Column are:
CUSTOMER, PLACE, DELIVERY_DATE, QTY, AMOUNT, ORDERN01, ORDERN02, MAIL, SITE
I want to sum QTY and AMOUNT of each ORDERN01.
Re: Problem Group by command [message #309629 is a reply to message #309607] Fri, 28 March 2008 03:54 Go to previous messageGo to next message
flyboy
Messages: 1832
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 #309638 is a reply to message #309628] Fri, 28 March 2008 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is ORDERN01 a key for the rest of your columns (but the aggregate ones)?
That is, if you have one ORDERN01 value, does this implies the value for the other columns (but the aggregate ones)? Or in the other to see it, can you have the same ORDERN01 for different CUSTOMER, PLACE, DELIVERY_DATE...?

Regards
Michel
Re: Problem Group by command [message #309665 is a reply to message #309607] Fri, 28 March 2008 06:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #309731 is a reply to message #309726] Fri, 28 March 2008 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not change anything but introduce computation errors.

Regards
Michel
Re: Problem Group by command [message #309738 is a reply to message #309731] Fri, 28 March 2008 11:20 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous messageGo to next message
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 #309745 is a reply to message #309607] Fri, 28 March 2008 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>big mistake (I did it on a hurry).
Ready, Fire, AIM!
Re: Problem Group by command [message #309748 is a reply to message #309745] Fri, 28 March 2008 12:12 Go to previous messageGo to next message
Tafer
Messages: 64
Registered: July 2005
Location: Here!
Member
Come on!

First answer gets the cookie, and the idea is there!

AND.. I'm leaving a small surprise to sinsay. Wink
Re: Problem Group by command [message #309750 is a reply to message #309748] Fri, 28 March 2008 12:16 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Mutating trigger error
Next Topic: Accessing a record´s column dynamically
Goto Forum:
  


Current Time: Fri Dec 02 14:20:21 CST 2016

Total time taken to generate the page: 0.11793 seconds