We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Using Group by on mm/yyyy

IUAATech
IUAATech asked
on
Medium Priority
694 Views
Last Modified: 2012-05-06
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 :-)
Comment
Watch Question

Commented:
Try this:
SELECT MONTH(DateField), YEAR(DateField), count(Jobs) FROM Table1 GROUP BY MONTH(DateField), YEAR(DateField) ORDER BY YEAR(DateField), MONTH(DateField)
CERTIFIED EXPERT
Awarded 2008
Awarded 2008
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
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.
Faizan SarwarSoftware / Database Developer
CERTIFIED EXPERT

Commented:

select right( convert(nvarchar, SessionCreationDate,103),7), count(*)
from Session
group  by  right(convert(nvarchar, SessionCreationDate,103),7)

Author

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?

Author

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

Commented:
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..

Author

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

Commented:
ORDER BY YEAR(CreatedOn), MONTH(CreatedOn)

Author

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
Faizan SarwarSoftware / Database Developer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
sm394,
your solution works. Thanks.

However, is there a way to not return 'dd' in the result set?
Software / Database Developer
CERTIFIED EXPERT
Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.