Solved

# SQL group by Date

Posted on 2009-04-28
256 Views
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
0
Question by:mcrmg

LVL 142

Accepted Solution

select CONVERT(VARCHAR(7), F2, 120), count(*) from yourtable group by  CONVERT(VARCHAR(7), F2, 120)
0

Author Comment

SELECT CONVERT(VARCHAR(7), '2/1/2009', 120)

it gives me 2/1/200
thanks
0

LVL 142

Expert Comment

is F2 varchar or datetime?
0

LVL 40

Expert Comment

try this.
``````select RIGHT(CONVERT(VARCHAR(10), F2,103), 7),COUNT(F1)

from YourTable

group by RIGHT(CONVERT(VARCHAR(10),F2,103),7)
``````
0

LVL 40

Expert Comment

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)
``````
0

Author Comment

it is a datetime field..thanks
0

Author Comment

SELECT CONVERT(VARCHAR(7), '2/1/2009', 103)

-->

2/1/200

thx
0

LVL 40

Expert Comment

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
``````
0

LVL 40

Assisted Solution

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
``````
0

LVL 142

Expert Comment

>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

Author Comment

tthank you
0

## Featured Post

### Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…