CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ENGR024
ENGR024

Reputation: 327

SQL Sum and Count Separated by Month

I am writing a script that query's a table and counts all rows that have a status of 10 and separates the total count by month. Something is not right considering I have two Decembers in my results.

In November, there is only one date in it meaning two rows have a status of 10 and are under the same date (11-04). December has 252 rows on the same date (12-04) and 1 row with a 12-05 date .

How to query and separate a count and date by months?

Any help is most appreciated. 

SELECT CONVERT(CHAR(3), Datename(month, datecomplete)) AS Month, 
       Count(*)                                        AS Val 
FROM   nwds 
WHERE  status = 10 
GROUP  BY Datediff(month, 0, datecomplete), 
          datecomplete

My Results

Nov 2
Dec 252
Dec 1

Desired Results

Nov 2
Dec 253

Upvotes: 0

Views: 1945

Answers (3)

shaikh
shaikh

Reputation: 129

Try this

select CONVERT(char(3),DATENAME(MONTH,datecomplete)) as [month],
       count(*)                                      as val
from nwds
WHERE  status = 10 
group by CONVERT(char(3),DATENAME(MONTH,datecomplete))

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

datecomplete should be excluded from group by, or the results would be grouped by day. Grouping needs to be on month and year part of datecomplete column.

SELECT 
CONVERT(CHAR(3), Datename(month, datecomplete)) AS Month, 
count(*)  AS Val 
FROM   nwds 
WHERE  status = 10 
GROUP  BY CONVERT(CHAR(3), Datename(month, datecomplete)) ,
datepart(yyyy, datecomplete)

Upvotes: 1

Ben Rubin
Ben Rubin

Reputation: 7371

SELECT LEFT(DATENAME(M, datecomplete), 3) AS Month, 
       Count(*)                           AS Val 
FROM   nwds 
WHERE  status = 10 
GROUP  BY LEFT(DATENAME(M, datecomplete), 3)

If you have ever get data for different years, you can add the year to the GROUP BY.

SELECT LEFT(DATENAME(M, datecomplete), 3) AS Month, 
       YEAR(datecomplete)                 AS Year,
       Count(*)                           AS Val 
FROM   nwds 
WHERE  status = 10 
GROUP  BY LEFT(DATENAME(M, datecomplete), 3), YEAR(datecomplete)

Upvotes: 1

Related Questions