SQL group by Date

Hi,

I have some data looks like the following:
F1                 F2
somedata     2/1/2009
somedata     2/10/2009
somedata     2/15/2008
somedata     3/1/2009
somedata     3/18/2009
somedata     2/1/2009
How can I group find out the count for the records that are in the same month of the same year? So the outcome will look like this:
2/2008  1
2/2009  3
3/2009  2


thx
mcrmgAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
select CONVERT(VARCHAR(7), F2, 120), count(*) from yourtable group by  CONVERT(VARCHAR(7), F2, 120)
0
 
mcrmgAuthor Commented:
SELECT CONVERT(VARCHAR(7), '2/1/2009', 120)

it gives me 2/1/200
thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is F2 varchar or datetime?
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
SharathData EngineerCommented:
try this.
select RIGHT(CONVERT(VARCHAR(10), F2,103), 7),COUNT(F1)
  from YourTable
 group by RIGHT(CONVERT(VARCHAR(10),F2,103),7)

Open in new window

0
 
SharathData EngineerCommented:

The above query works if your column F2 is of datetime. If it is a varchar type column, then try this query.
select RIGHT(CONVERT(VARCHAR(10), convert(datetime,F2),103), 7),COUNT(F1)
  from YourTable
 group by RIGHT(CONVERT(VARCHAR(10), convert(datetime,F2),103), 7)

Open in new window

0
 
mcrmgAuthor Commented:
it is a datetime field..thanks
0
 
mcrmgAuthor Commented:
SELECT CONVERT(VARCHAR(7), '2/1/2009', 103)

-->

2/1/200


thx
0
 
SharathData EngineerCommented:

Please try like the given query.
In your last post, you are applying CONVERT funciton on a varchar type value. first convert it to datetime type and then apply the CONVERT function.
See the below examples for better understanding.
SELECT CONVERT(VARCHAR(10), convert(datetime,'2/1/2009'), 103)
-- 01/02/2009 DD/MM/YYYY format
SELECT RIGHT(CONVERT(VARCHAR(10), convert(datetime,'2/1/2009'), 103),7)
-- 02/2009 MM/YYYY

Open in new window

0
 
SharathConnect With a Mentor Data EngineerCommented:
In order to run angelIII query, you have to try like this.
Based on your data type (datetime or varchar), you need to apply the CONVERT one more time.

SELECT CONVERT(VARCHAR(7), convert(datetime,'2/1/2009'), 120)
-- 2009-02 YYYY-MM

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>it is a datetime field..thanks
then, just apply my query as posted in the first comment.

to try out with a single value, try out the query given by Sharath in html://#a24257088
0
 
mcrmgAuthor Commented:
tthank you
0
All Courses

From novice to tech pro — start learning today.