SQL query for generating given data

Question

I have a table called student with two columns:

JoinDate DATE
EmployeeName STRING

The contents of the table are as follows:

EmployeeName    | JoinDate
----------------+-----------------
jaison          | 1-jan-2008
robin           | 2-feb-2008
binoy           | 3-mar-2008
rahul           | 4-feb-2008

I am looking to prepare the following output based on this table: a table containing 4-columns with names jan,feb,mar,april. Beneath each of these months a count is given as 1,2,1,0. These counts represent the number of employees that joined in that month (January 1 employee, February 2 employees, March 1 employee, April 0 employees)

Can you give me the required SQL query?

Answer 1

The following query will give you a result set of two columns (month and total), where month is the month name and total is the number of people that joined that month:

SELECT MONTHNAME(`join_date`) AS `month`, COUNT(*) AS `total` GROUP BY MONTHNAME(`join_date`);

Answer 2

This will work, anyone know any other way?

SELECT Sum([1]) AS Jan, Sum([2]) AS Feb, Sum([3]) AS Mar, Sum([4]) as Apr,
       Sum([5]) as May, Sum([6]) as Jun, Sum([7]) as Jul, Sum([8]) as Aug,
       Sum([9]) as Sep, Sum([10]) as Oct, Sum([11]) as Nov, Sum([12]) as Dec
FROM (SELECT Month(Join_Date) as Mon FROM student) ps
PIVOT
    (Count(Mon) FOR Mon IN 
        ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS pvt
GROUP BY [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

Answer 3

This also will work

Create table [sample] (Employeename varchar(50), join_date smalldatetime) insert into [sample] values('Ganesh','01/26/2008') insert into [sample] values('Ramesh','02/26/2008') insert into [sample] values('Dinesh','03/26/2008') insert into [sample] values('Suresh','01/26/2008')

Select * from [sample]

SELECT 'Students Admission by Monthwise' ,[1] AS january,[2] AS february,[3] AS March,[4] AS April FROM( SELECT Employeename, Month(join_date) monthname FROM [sample] ) A PIVOT ( COUNT(Employeename) FOR monthname in ([1],[2],[3],[4]) ) AS PVT