CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Learning
Learning

Reputation: 20051

SQL query to count based on MM-YYYY part of date

I need to show count of job application for last 12 months based on & need to group them also based on Job Application dates

I tried following it generates error as shown below.

SELECT 
   COUNT(MONTH(ApplicationDate) AS VARCHAR(2)) + '-' + CAST(YEAR(ApplicationDate) AS VARCHAR(4)) AS Total, 
   ApplicationDate 
FROM 
   [Jobs] 
GROUP BY 
   ApplicationDate, ApplicationDate 
ORDER BY
   ApplicationDate

But that resulted in an error:

Msg 195, Level 15, State 10, Line 9
'COUNT' is not a recognized built-in function name.

I need it for SQL Server 2008

Final Working solution

SELECT   
MONTH(ApplicationDate),   YEAR(ApplicationDate),   COUNT(*) AS Total, 
    (CAST(MONTH(ApplicationDate) AS VARCHAR(2)) + '-' + CAST(YEAR(ApplicationDate) AS VARCHAR(4)))  as ApplicationDate
    FROM    [Jobs] 
GROUP BY    YEAR(ApplicationDate),   MONTH(ApplicationDate)

ORDER BY
       YEAR(ApplicationDate),   MONTH(ApplicationDate)

Upvotes: 0

Views: 2304

Answers (3)

marc_s
marc_s

Reputation: 755421

You need to try something like this to get one row for each month/year that contains the count of applications for that month/year:

SELECT
   MONTH(ApplicationDate),
   YEAR(ApplicationDate),
   COUNT(*)
FROM 
   dbo.[Jobs] 
GROUP BY 
   YEAR(ApplicationDate),
   MONTH(ApplicationDate)
ORDER BY
   YEAR(ApplicationDate),
   MONTH(ApplicationDate)

This counts the applications per month / year and groups by it, too. You should get an output something like this:

enter image description here

(this is taken from the AdventureWorks sample database)

If you do a GROUP BY ApplicationDate, then you're basically grouping / counting by the actual date itself (not it's month/year parts)

Update: if you must have SQL Server provide the formatting in the MM-YYYY format (really should be done in your web app and not by SQL Server), then try this:

;WITH GroupedData AS
(
    SELECT
       DateMonth = MONTH(ApplicationDate),
       DateYear = YEAR(ApplicationDate),
       JobsCount = COUNT(*)
    FROM 
       dbo.[Jobs]
    GROUP BY 
       YEAR(ApplicationDate),
       MONTH(ApplicationDate)
)
SELECT
    RIGHT('00' + CAST(DateMonth AS VARCHAR(2)), 2) + '-' +
    CAST(DateYear AS VARCHAR(4)),
    JobsCount 
FROM 
    GroupedData
ORDER BY
    DateMonth, DateYear

Upvotes: 1

Praveen S
Praveen S

Reputation: 650

Try This... 

SELECT  DATENAME(month,ApplicationDate) + ' ' + CAST(DATEPART(year,ApplicationDate) AS VARCHAR(4)),
        COUNT(1) TotalCnt
FROM    dbo.[Jobs] 
GROUP BY DATENAME(month,ApplicationDate) + ' ' + CAST(DATEPART(year,ApplicationDate) AS VARCHAR(4))

Upvotes: 0

ray
ray

Reputation: 457

How about this one?

 SELECT 
       COUNT(*) AS Total, 
       (CAST(MONTH(ApplicationDate) AS VARCHAR(2)) + '-' + CAST(YEAR(ApplicationDate) AS VARCHAR(4)))  as ApplicationDate 
    FROM 
       [Jobs]  
    GROUP BY 
       (CAST(MONTH(ApplicationDate) AS VARCHAR(2)) + '-' + CAST(YEAR(ApplicationDate) AS VARCHAR(4))) 
    ORDER BY
      ApplicationDate

the cast function was missing for month. Output will be as follows.

Total   ApplicationDate
22217   7-2012
17979   8-2012
30341   9-2012

Edited for sorting...

If you are not particular about the 'MM-YYYY' format. Convert the month/year combination to number and order by that. Try this.

 SELECT 
   COUNT(*) AS Total, 
   RIGHT(CONVERT(VARCHAR(10), ApplicationDate, 103), 7)  as ApplicationDate, 
   CAST(REPLACE(RIGHT(CONVERT(VARCHAR(10), ApplicationDate, 103), 7), '/', '') as int) as numdate
FROM 
   [Jobs] 
GROUP BY 
   RIGHT(CONVERT(VARCHAR(10), ApplicationDate, 103), 7)
ORDER BY
  numdate,ApplicationDate

Upvotes: 0

Related Questions