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

Using Group by on mm/yyyy

I am trying to use group by on just mm/yyyy.

I wish to display the data in a chart as follows:

mm/yyyy on the x axis and the count of the jobs for that period on the y axis.

Any help with writing the query is appreciated :-)
0
IUAATech
Asked:
IUAATech
  • 5
  • 3
  • 2
  • +3
2 Solutions
 
OtanaCommented:
Try this:
SELECT MONTH(DateField), YEAR(DateField), count(Jobs) FROM Table1 GROUP BY MONTH(DateField), YEAR(DateField) ORDER BY YEAR(DateField), MONTH(DateField)
0
 
chapmandewCommented:
you could do something like this:

select cast(datepart(m, datefield) as varchar(5))+ '/' + cast(datepart(yyyy, datefield) as varchar(5)), job, count(*)
from tablename
group by cast(datepart(m, datefield) as varchar(5))+ '/' + cast(datepart(yyyy, datefield) as varchar(5)), job

group by
0
 
SwamyNCommented:
the information you have provided is quite vague. Queries retrieve data from tables. It would be helpful if you could provide sample data in the tables and the kind of output you are expecting.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
sm394Commented:

select right( convert(nvarchar, SessionCreationDate,103),7), count(*)
from Session
group  by  right(convert(nvarchar, SessionCreationDate,103),7)
0
 
IUAATechAuthor Commented:
Thanks guys.

chapmandew's solution works for me.

However, I also need to do a "order by" on the date.

Since the query is returning a varchar, "order by" sorts based on varchar, not datetime.

How can I fix this?
0
 
IUAATechAuthor Commented:
here is a sample of what I get when I use ORDER BY:

1/2006      81
1/2007      145
1/2008      79
10/2005      16
10/2006      146
10/2007      140
10/2008      63
11/2005      77
11/2006      134
0
 
reb73Commented:
Change the select statement in chapmandew's solution as follows -

select right('0' + cast(datepart(m, datefield) as varchar(5)), 2) + '/' + cast(datepart(yyyy, datefield) as varchar(5)), job,


This prefixes a 0 to single digit months which will order correctly..

0
 
IUAATechAuthor Commented:
That didn't work.

here is my query:
SELECT     RIGHT('0' + CAST(DATEPART(m, CreatedOn) AS varchar(5)), 2) + '/' + CAST(DATEPART(yyyy, CreatedOn) AS varchar(5)) AS DatePeriod, COUNT(*) AS TotalJobs
FROM         dbo.ComputerSupportRequest
GROUP BY RIGHT('0' + CAST(DATEPART(m, CreatedOn) AS varchar(5)), 2) + '/' + CAST(DATEPART(yyyy, CreatedOn) AS varchar(5))
ORDER BY dateperiod

and I got the following:
01/2006      81
01/2007      145
01/2008      79
02/2006      91
02/2007      133
02/2008      125
03/2005      5
03/2006      67
03/2007      81
03/2008      79
04/2005      9
04/2006      66
04/2007      85
04/2008      82
05/2005      17
05/2006      73
05/2007      98
05/2008      167
0
 
OtanaCommented:
ORDER BY YEAR(CreatedOn), MONTH(CreatedOn)
0
 
IUAATechAuthor Commented:
If I am going to use "CreatedOn" in ORDER BY, then it has to exist in GROUP BY also.

So, my query now looks like
SELECT     RIGHT('0' + CAST(DATEPART(m, CreatedOn) AS varchar(5)), 2) + '/' + CAST(DATEPART(yyyy, CreatedOn) AS varchar(5)) AS DatePeriod, COUNT(*) AS TotalJobs
FROM         dbo.ComputerSupportRequest
GROUP BY RIGHT('0' + CAST(DATEPART(m, CreatedOn) AS varchar(5)), 2) + '/' + CAST(DATEPART(yyyy, CreatedOn) AS varchar(5)), CreatedOn
ORDER BY YEAR(CreatedOn), MONTH(CreatedOn)

and here is what I get:
03/2005      1
03/2005      1
03/2005      1
03/2005      1
03/2005      1
04/2005      1
04/2005      1
04/2005      1
04/2005      1
04/2005      1
04/2005      1
04/2005      1
04/2005      1
0
 
sm394Commented:
select
d.dt,
d.cnt,
convert(datetime, '1/'+d.dt,103) as dd
from
(
      select  right ( convert(nvarchar, SessionCreationDate,103),7) as dt, count(*) as cnt
      from Session
      group  by   right(convert(nvarchar, SessionCreationDate,103),7)
) d
order by dd asc

0
 
IUAATechAuthor Commented:
sm394,
your solution works. Thanks.

However, is there a way to not return 'dd' in the result set?
0
 
sm394Commented:
select t1.dt,t1.cnt
from(
      select
      d.dt,
      d.cnt,
      convert(datetime, '1/'+d.dt,103) as dd
      from
      (
            select  right ( convert(nvarchar, SessionCreationDate,103),7) as dt, count(*) as cnt
            from Session
            group  by   right(convert(nvarchar, SessionCreationDate,103),7)
      ) d)t1
order by t1.dd asc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 5
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now