Group by month/Year in a SQL view

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
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 Commented:
Thanks chaps, got what i needed from both of you
0
Commented:
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
Commented:

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.