Suppose I have an employee table with a column as DateOfJoining. And I have entered the data as

2019-12-4
2019-12-6
2019-12-5
2019-10-5
2010-08-17

Now I want write the SQL query to find the month in which there is maximum number of joinees.

3 Answers

0
fa06 On

You can try below - using aggregation and TOP

select top 1 month(dateofjoining),count(*) as totaljoining
from tablename
where year(dateofjoining)=2019
group by month(dateofjoining)
order by 2 desc
0
Community On

Using Derived table and row_number()

Below query will give you the month in which there is maxmium number of joinees for each year.

select cnt,mnth,yr
from
(select count(DateOfJoining)cnt,
        month(DateOfJoining)mnth,
        year(DateOfJoining)yr,
        row_number()over(partition by year(DateOfJoining) order by count(DateOfJoining)desc)srno
 from #employee
 group by month(DateOfJoining),year(DateOfJoining) 
)tbl
where srno = 1

output

cnt         mnth        yr
----------- ----------- -----------
1           8           2010
3           12          2019

and if you want specifically for 2019 then add the condition yr ='2019' in the where clause.

where srno = 1
and yr =2019

output

cnt         mnth        yr
----------- ----------- -----------
3           12          2019
0
Gordon Linoff On

You want both the biggest and least -- although I am guessing that you want at least one employee.

with e as (
      select year(dateofjoining) as yyyy,
             month(dateofjoining) as mm,
             count(*) as totaljoining
      from employee
      where dateofjoining >= '2019-01-01' and
            dateofjoining < '2020-01-01'
      group by year(dateofjoining), month(dateofjoining)
     )
select e.*
from ((select top (1) e.*
       from e
       order by totaljoining asc
      ) union all
      (select top (1) e.*
       from e
       order by totaljoining desc
      ) 
     ) e;

Notes:

  • The date comparison uses direct comparisons to dates rather than using functions. This is a best practice so the optimizer can use indexes.
  • You want both the smallest and biggest value, so this uses a CTE so the group by is represented only once.
  • This will not return months with no employees.
  • If you want ties, then use top (1) with ties.