Home » SQL & PL/SQL » SQL & PL/SQL » group by and order by (oralce 9i)
group by and order by [message #417862] Tue, 11 August 2009 22:18 Go to next message
cesarnz
Messages: 10
Registered: November 2008
Junior Member
hi all,
i have an sql statement where i gather all the employees who have sold a product given a certain time period. I can get the data without much problem but the results look like they could be better "grouped". I get this at the moment :


employeename, productID, productType, date, soldAmount

tom 100 1 12/08/2009 1
tom 100 1 12/08/2009 1
tom 100 1 12/08/2009 1
tom 100 1 12/08/2009 1
tom 102 4 12/08/2009 1
tom 102 4 12/08/2009 1



Looking at the results i can see tom sold 4 products (id=100) so i wanted to display something like :



employeename, productID, productType, date, soldAmount

tom 100 1 12/08/2009 4
tom 102 4 12/08/2009 2


any suggestions on how to do this ?? sql so far is :

select employee.name, product.productID, product.productType, sales.date, count(product.productType) "soldAmount"
FROM employee, product, sales WHERE ....
GROUP BY employee.name, product.productID, product.productType, sales.date
ORDER BY count(product.productType)





Re: group by and order by [message #417863 is a reply to message #417862] Tue, 11 August 2009 22:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: group by and order by [message #417865 is a reply to message #417863] Tue, 11 August 2009 22:32 Go to previous messageGo to next message
cesarnz
Messages: 10
Registered: November 2008
Junior Member
hi,
unfortunately i cant show you the results of the sqlplus session etc since the data is sensitive to a company.

Anyway thanks for your help.

Re: group by and order by [message #417867 is a reply to message #417862] Tue, 11 August 2009 22:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Nothing prevent you from providing DDL for table & DML for sample test data or from using <code tags> to make your posts readable!
Re: group by and order by [message #417896 is a reply to message #417862] Wed, 12 August 2009 01:09 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:

any suggestions on how to do this ?? sql so far is :

select employee.name, product.productID, product.productType, sales.date, count(product.productType) "soldAmount"
FROM employee, product, sales WHERE ....
GROUP BY employee.name, product.productID, product.productType, sales.date
ORDER BY count(product.productType)


USE SUM IN PLACE OF COUNT
Re: group by and order by [message #417904 is a reply to message #417896] Wed, 12 August 2009 01:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ayush_anand wrote on Wed, 12 August 2009 08:09


USE SUM IN PLACE OF COUNT

No need to shout.
Typing in capitals is considered shouting, adding bold face only make things worse
Re: group by and order by [message #417905 is a reply to message #417896] Wed, 12 August 2009 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why did you put in UPPER case?
Why did you put it in bold?
What make you shout?

Regards
Michel
Re: group by and order by [message #417909 is a reply to message #417904] Wed, 12 August 2009 01:40 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
No need to shout.
Typing in capitals is considered shouting, adding bold face only make things worse


Sorry I didnt know this rule

[Updated on: Wed, 12 August 2009 01:41]

Report message to a moderator

Re: group by and order by [message #417933 is a reply to message #417909] Wed, 12 August 2009 03:30 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
ayush_anand wrote on Wed, 12 August 2009 07:40
Quote:
No need to shout.
Typing in capitals is considered shouting, adding bold face only make things worse


Sorry I didnt know this rule

From the Posting guidelines:

Quote:
Don't use uppercase only. It's harder to read and gives the impression you're shouting

Re: group by and order by [message #417937 is a reply to message #417862] Wed, 12 August 2009 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you can count (or sum) productType to give soldAmount then that column is wildly miss-named.
Re: group by and order by [message #417942 is a reply to message #417937] Wed, 12 August 2009 04:05 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SUM(soldamount) is what I meant Smile
Re: group by and order by [message #418005 is a reply to message #417862] Wed, 12 August 2009 08:11 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
cesarnz wrote on Tue, 11 August 2009 23:18

select employee.name, product.productID, product.productType, sales.date, count(product.productType) "soldAmount"
FROM employee, product, sales WHERE ....
GROUP BY employee.name, product.productID, product.productType, sales.date
ORDER BY count(product.productType)



And what output do you get from this?
Re: group by and order by [message #418052 is a reply to message #417862] Wed, 12 August 2009 14:37 Go to previous messageGo to next message
cesarnz
Messages: 10
Registered: November 2008
Junior Member
hi all, thanks for your reply. sorry should have explained my problem better. let me try again :



i have an sql statement where i gather all the employees who have sold a product given a certain time period. I can get the data without much problem but the results look like they could be better "grouped". I get this at the moment :


employeename, productID, productType, date, soldAmount

tom 100 1 12/08/2009 1
tom 100 1 12/08/2009 1
tom 100 1 12/08/2009 1
tom 100 1 12/08/2009 1
tom 102 4 12/08/2009 1
tom 102 4 12/08/2009 1



Looking at the results i can see tom sold 4 products (id=100) so i wanted to display something like :



employeename, productID, productType, date, soldAmount

tom 100 1 12/08/2009 4
tom 102 4 12/08/2009 2


sql so far is :

select employee.name, product.productID, product.productType, sales.date, count(product.productType) "soldAmount"
FROM employee, product, sales WHERE ....
GROUP BY employee.name, product.productID, product.productType, sales.date
ORDER BY count(product.productType)


have also tried using sum instead of count


select employee.name, product.productID, product.productType, sales.date, sum(product.productType) "soldAmount"
FROM employee, product, sales WHERE ....
GROUP BY employee.name, product.productID, product.productType, sales.date
ORDER BY count(product.productType)

but the results show up as

employeename, productID, productType, date, soldAmount

tom 100 1 12/08/2009 6
tom 100 1 12/08/2009 6
tom 100 1 12/08/2009 6
tom 100 1 12/08/2009 6
tom 102 4 12/08/2009 6
tom 102 4 12/08/2009 6



thanks again.
Re: group by and order by [message #418054 is a reply to message #417862] Wed, 12 August 2009 14:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
still no DDL for tables
still no DML for test data
still unformatted

So, still no assistance available at this time.
Re: group by and order by [message #418062 is a reply to message #418052] Wed, 12 August 2009 15:58 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Try selecting and grouping by trunc(sales.date) instead.. If the dates have different time components they won't be grouped together.

Anyways, summing together the product type does not make sense, so then you should rather go with count.
Re: group by and order by [message #418104 is a reply to message #418052] Wed, 12 August 2009 23:29 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
select employee.name, product.productID, product.productType, sales.date, sum(product.productType) "soldAmount"
FROM employee, product, sales WHERE ....
GROUP BY employee.name, product.productID
ORDER BY count(product.productType) 


Please Note: what is the total sold amount will be decided by you? For me I think it is sum of daily sold amount by an individual for one product type
Previous Topic: Tune the query
Next Topic: Skipping Records in a cursor loop
Goto Forum:
  


Current Time: Fri Dec 09 15:38:08 CST 2016

Total time taken to generate the page: 0.22459 seconds