Solved

# Group by month/Year in a SQL view

Posted on 2009-06-29
1,148 Views
Hello all

I have a SOL table with dates in. I need to group by month and year and get a count all within a view if possible. Any ideas please

Many thanks
0
Question by:bostonste
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 250 total points
ID: 24735152
this should be a good starter:
``````select convert(varchar(7), your_date_field, 120) yyy_mn , count(*), sum(somefield)
from yourtable
group by convert(varchar(7), your_date_field, 120)
``````
0

LVL 22

Assisted Solution

8080_Diver earned 250 total points
ID: 24735334
Alternatively, if you want your month and year separately displayed, you could try:
``````SELECT MyYear,
MyMonth,
COUNT(*) AS MonthYearCount
FROM
(
SELECT CONVERT(VARCHAR(4), your_date_field, 120) AS MyYear,
CONVERT(VARCHAR(2), your_date_field, 110) AS MyMonth
FROM   yourtable
) Z
GROUP BY MyYear,
MyMonth;
``````
0

Author Closing Comment

ID: 31597868
Thanks chaps, got what i needed from both of you
0

LVL 17

Expert Comment

ID: 24735352
I usually use this formula to get the vaue in YYYYMM format because it is easier when you sort. Sorts by first Year and then by Month:
``````SELECT  Year(Date)*100+Month(Date) as YearMth, SUM(Columns) Column
From    TableName
Group by Year(Date)*100+Month(Date)
Order by Year(Date)*100+Month(Date)
``````
0

LVL 1

Expert Comment

ID: 24747506

SELECT
CLIENT=MYCLIENTNAME,
HOWMANY=COUNT(*),
COST=SUM(MYCOST),
nMonth=DATEPART(month,MYDATE),
nYEAR=DATEPART(year,MYDATE)

FROM
MYTABLE

GROUP BY MYCLIENTNAME, DATEPART(month,MYDATE), DATEPART(year,MYDATE)

ORDER BY MYCLIENTNAME, DATEPART(month,MYDATE), DATEPART(year,MYDATE)
Hope this help!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question