Count records for every month in a year

Question

I have a table with total no of 1000 records in it It has the following structure   EMP ID EMP NAME PHONE NO   ARR DATE 1        A        545454 2012 03 12   I want to calculate no of records for every month in year-2012   Is there any way that should solve my issue in a single shot   I tried    select count     from table emp  where year ARR DATE     2012  and month ARR DATE     01

User · Accepted Answer

SELECT    COUNT     FROM      table emp  WHERE     YEAR ARR DATE     2012   GROUP BY  MONTH ARR DATE

User · Answer

Try This query   SELECT    SUM CASE datepart month ARR DATE  WHEN 1 THEN 1 ELSE 0 END  AS  January     SUM CASE datepart month ARR DATE  WHEN 2 THEN 1 ELSE 0 END  AS  February     SUM CASE datepart month ARR DATE  WHEN 3 THEN 1 ELSE 0 END  AS  March     SUM CASE datepart month ARR DATE  WHEN 4 THEN 1 ELSE 0 END  AS  April     SUM CASE datepart month ARR DATE  WHEN 5 THEN 1 ELSE 0 END  AS  May     SUM CASE datepart month ARR DATE  WHEN 6 THEN 1 ELSE 0 END  AS  June     SUM CASE datepart month ARR DATE  WHEN 7 THEN 1 ELSE 0 END  AS  July     SUM CASE datepart month ARR DATE  WHEN 8 THEN 1 ELSE 0 END  AS  August     SUM CASE datepart month ARR DATE  WHEN 9 THEN 1 ELSE 0 END  AS  September     SUM CASE datepart month ARR DATE  WHEN 10 THEN 1 ELSE 0 END  AS  October     SUM CASE datepart month ARR DATE  WHEN 11 THEN 1 ELSE 0 END  AS  November     SUM CASE datepart month ARR DATE  WHEN 12 THEN 1 ELSE 0 END  AS  December     SUM CASE datepart year ARR DATE  WHEN 2012 THEN 1 ELSE 0 END  AS  TOTAL  FROM     sometable WHERE   ARR DATE BETWEEN  2012 01 01  AND  2012 12 31

User · Answer

This will give you the count per month for 2012   SELECT MONTH ARR DATE  MONTH  COUNT    COUNT FROM table emp WHERE YEAR arr date  2012 GROUP BY MONTH ARR DATE     Demo here

User · Answer

select count     from table emp   where DATEPART YEAR  ARR DATE     2012  AND DATEPART MONTH  ARR DATE     01

[sql] Count records for every month in a year

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008