58

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'
Gangnus
  • 24,044
  • 16
  • 90
  • 149
manoj kumar singh
  • 781
  • 3
  • 8
  • 14
  • 1
    there doesn't appear to be a question? – Mitch Wheat Mar 27 '12 at 11:00
  • 1
    see from..... I want to (2nd line) from top for question – manoj kumar singh Mar 27 '12 at 11:01
  • 1
    BTW Wrapping columns in functions makes the query unsargable. – Martin Smith Mar 27 '12 at 11:01
  • 2
    It would be helpful if you would take some time to state your question clearly and then format it correctly to make it readable. In this case, I think you are asking how to count the number of records in each month of one year, based on the ARR_DATE column but this is a guess. If it's correct, check the documentation for GROUP BY, COUNT(), YEAR() and MONTH(). – Pondlife Mar 27 '12 at 11:04

4 Answers4

106
SELECT    COUNT(*) 
FROM      table_emp 
WHERE     YEAR(ARR_DATE) = '2012' 
GROUP BY  MONTH(ARR_DATE)
Dan
  • 45,079
  • 17
  • 88
  • 157
  • why quotes around 2012? `YEAR()` returns a number AFAIK. – iDevlop Jun 01 '16 at 10:37
  • 1
    @iDevlop I just tested it (`Microsoft SQL Server 2012 - 11.0.5058.0 (X64)`)and it works both with and without the quotes. – Dan Jun 01 '16 at 11:11
  • 1
    ** eg:- April has not any employee registered.** so that month has 0 counts. how that record adds to the final result. – Janaka Pushpakumara Oct 14 '17 at 12:33
  • @janaka it would be absent from the result, if you're asking how to force it to include April, 0 I think you should ask it as a new question referencing this one – Dan Oct 15 '17 at 13:45
  • @Dan Hi Dan, Can you please tell me If I have to find the record between two years such that I have to show the count from Jan 2019 to Dec 2020 for each month seperately – Ayush Srivastava Dec 12 '20 at 05:32
  • @AyushSrivastava do you something like `where ARR_DATE between '01/01/2019' and '31/12/2020'` and then `group by year(ARR_DATE), month(ARR_DATE)`? – Dan Dec 13 '20 at 14:01
59

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.

Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294
22

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'
cichy
  • 10,464
  • 4
  • 26
  • 36
  • 1
    +1 for avoiding unsargable predicate but should use unambiguous date formats and the OP should avoid `BETWEEN` if `datetime` datatype (OK if `date` datatype) – Martin Smith Mar 27 '12 at 11:06
  • Assumed it is date type, since there is no "time" in example data – cichy Mar 27 '12 at 11:09
  • It will display total number of records from January to December....Monthwise or not..? – manoj kumar singh Mar 27 '12 at 11:09
  • @manojkumarsingh - It should correctly separate the values by month. I prefer Joachim's query because it is shorter. But both are valid and should produce the same *counts*, just one in rows and the other in columns. – Leigh Mar 27 '12 at 19:20
  • @Leigh small difference between Joachim's and mine queries is that, mine in extremly faster over very large tables, since it doesn't use GROUP BY ;) – cichy Feb 05 '17 at 08:25
  • @cichy - Interesting. I would have thought performance would be similar, since the net effect is similar, but I could be wrong :). If it is really faster, I would be curious to read an explanation of why, so if you know of any links explaining it, feel free to post them. – Leigh Feb 05 '17 at 15:30
  • @Leigh I don't have any links at the moment. Few years ago i had to use similar query on a table with over 100 milion rows. With Group BY query was running longer than a minute, and with SUM() it was running less than second. As far as i remember it was a matter of using indexes and temporary tables. Sum() was working only on indexes, and group by had to create temporary table etc. – cichy Feb 06 '17 at 11:50
  • @Leigh as a clarification, above performance was about MySQL, You can check how it will behave on SqlServer: MySQL Explain: http://pastebin.com/2yqK9jdW – cichy Feb 06 '17 at 12:13
  • How can we make it column wise like Month | Count columns only – U.Malik Oct 17 '19 at 13:25
2
select count(*) 
from table_emp 
 where DATEPART(YEAR, ARR_DATE) = '2012' AND DATEPART(MONTH, ARR_DATE) = '01'
Darren
  • 68,902
  • 24
  • 138
  • 144