asked on

sql query - group data by year (sql server 2000)

hi,

hopefully someone can help with the following sql query. i'm using sql server 2000.

i'm looking to return monthly data for 2 years (say 2006/2007) so that it can be charted.

so far i can return a single year's data but i need to be able to retrieve data for both years in a single query.

the query looks like this so far :

select sum(job_effort) as sum2006, datename(month, job_close_date) as monthname from jobs
WHERE year(job_close_date) = 2006
group by datename(month, job_close_date), month(job_close_date)
order by month(job_close_date)

as you can see, i simply want to sum the job_effort by the job_close_date - a single column for 2006 data and another for 2007 data.

can someone point me in the right direction ?

thanks in advance

mrdo!

Aneesh

select sum(job_effort) as sum2006, year(job_close_date) as [Year]
from jobs
group by year(job_close_date)
order by year(job_close_date)

craigc

ASKER

thanks aneeshattingal but i need the data grouped into months as well :

i was hoping to have :

2006 / 207 / month
10 / 15 / jan
15 / 20 / feb
20 / 15 / mar

many thanks for your input ...

mrdo!

D B

try
select (select sum(job_effort from jobs where year(job_close_date) = 2006) as y2006,
(select sum(job_effort from jobs where year(job_close_date) = 2006) as y2006,
datename(month, job_close_date) as monthname
from jobs
group by datename(month, job_close_date)
order by 3

D B

oder by month(job_close_date)

craigc

ASKER

hi dbbishop,

the following ;

select
(select sum(job_effort) from jobs where year(job_close_date) = 2006) as y2006,
(select sum(job_effort) from jobs where year(job_close_date) = 2007) as y2007,
datename(month, job_close_date) as monthname
from jobs
group by datename(month, job_close_date)
order by month(job_close_date)

gives me :

Column name 'jobs.job_close_date' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

select
(select sum(job_effort) from jobs where year(job_close_date) = 2006) as y2006,
(select sum(job_effort) from jobs where year(job_close_date) = 2007) as y2007,
datename(month, job_close_date) as monthname
from jobs
group by datename(month, job_close_date), job_close_date
order by month(job_close_date)

gives me the same values for each month and some of the months 4 times.

many thanks for your input though - much appreciated.

mrdo!

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

craigc

ASKER

many, many thanks Lowfatspread, excellent stuff !

regards

mrdo!