Home » SQL & PL/SQL » SQL & PL/SQL » Grouping problem by year
Grouping problem by year [message #231651] Tue, 17 April 2007 22:08 Go to next message
setvb
Messages: 1
Registered: April 2007
Location: san diego
Junior Member
I'm using oracle for a class. I'm having problems getting the query to group by the date_shipped year.

create or replace force view sales_by_years (sales_year, total_orders, total_shipped, total_sales)
as
select to_char(s.date_shipped, 'YYYY'), sum(s.qty_ordered), sum(s.qty_shipped), sum(t.price * s.qty_ordered)
from salesdetails s, titles t
where s.title_id = t.title_id
group by s.date_shipped, s.qty_ordered, s.qty_shipped
order by date_shipped

the results lists everything (21 lines), however it should only list the sums for each value for each year (2 lines):

ie.
2007, 390, 380, 5039.99
2006, 384, 384, 4833.23
Re: Grouping problem by year [message #231678 is a reply to message #231651] Wed, 18 April 2007 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at EXTRACT function.

Regards
Michel
Re: Grouping problem by year [message #231689 is a reply to message #231651] Wed, 18 April 2007 02:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You group by the date instead of the year of the date. That causes the query to return a record for each individual date.
Re: Grouping problem by year [message #231820 is a reply to message #231651] Wed, 18 April 2007 09:05 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
SORRY FOR ALL THE EDITS... I KEEP SEEING PROBLEMS

You may not have an order clause in a view.

create or replace force view sales_by_years (sales_year, total_orders, total_shipped, total_sales)
as
select TO_CHAR(s.date_shipped, 'YYYY') year, sum(s.qty_ordered) ordered, sum(s.qty_shipped) shipped, sum(t.price * s.qty_ordered) total
from salesdetails s, titles t
where s.title_id = t.title_id
group by TO_CHAR(s.date_shipped,'YYYY')

[Updated on: Wed, 18 April 2007 09:10]

Report message to a moderator

Re: Grouping problem by year [message #231832 is a reply to message #231820] Wed, 18 April 2007 09:38 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Better use EXTRACT instead of TO_CHAR to keep the NUMBER type (a year is a number not a string).

Regards
Michel


Previous Topic: pl/sql code
Next Topic: using API's in datafile conversion
Goto Forum:
  


Current Time: Fri Dec 09 11:45:37 CST 2016

Total time taken to generate the page: 0.10738 seconds