• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 837
  • Last Modified:

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!

0
craigc
Asked:
craigc
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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)
0
 
craigcAuthor Commented:
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!
0
 
dbbishopCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dbbishopCommented:
oder by month(job_close_date)
0
 
craigcAuthor Commented:
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!
0
 
LowfatspreadCommented:
select sum(case year(job_close_date)  when 2006 then job_effort else 0 end) as sum2006
          ,sum(case year(job_close_date)  when 2007 then job_effort else 0 end) as sum2007
      ,  M.Mthname
from jobs as J
right outer join (select 'Jan' as MthName,1 as mthno
union select 'Feb',2 union select 'Mar',3
union select 'Apr',4 union select 'May',5
union select 'Jun',6 union select 'Jul',7
union select 'Aug',8 union select 'Sep',9
union select 'Oct',10 union select 'Nov',11 union select 'Dec',12) as M  
on month(j.job_close_date) = m.mthno
Where Year(j.job_close_date) in (2006,2007)
group by M.Mthname
order by M.Mthno
0
 
craigcAuthor Commented:
many, many thanks Lowfatspread, excellent stuff !

regards

mrdo!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now