?
Solved

Group by month/Year in a SQL view

Posted on 2009-06-29
5
Medium Priority
?
1,152 Views
Last Modified: 2012-05-07
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
Comment
Question by:bostonste
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 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) 

Open in new window

0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 1000 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;

Open in new window

0
 

Author Closing Comment

by:bostonste
ID: 31597868
Thanks chaps, got what i needed from both of you
0
 
LVL 17

Expert Comment

by:pssandhu
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)

Open in new window

0
 
LVL 1

Expert Comment

by:angelsergio_e
ID: 24747506

How about this:

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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A method of moving multiple mailboxes (in bulk) to another database in an Exchange 2010/2013/2016 environment...
I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Watch the video of Kernel Migrator for SharePoint, which demonstrate the process easily of migration from SharePoint to SharePoint, OneDrive for Business & Google Drive servers, Public Folder to SharePoint, File Server to SharePoint. The tool has va…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question