CODEWITHSUNDEEP
sql-server
Milson
Milson

Reputation: 1575

To Group By Hire Date in SQL Server?

Assume the following table

Employee

EmployeeID           INT
FirstName            VARCHAR(50)
LastName             VARCHAR(50)
SupervisorEmployeeID INT
Salary               MONEY
HireDate             DATETIME

  1. How to show the number of employees hired per year for the last 5 years also include the average salary for employees hired in those years.

  2. How to show the number of employees hired per year for the last 5 years also include the average salary for employees hired in those years.

Upvotes: 0

Views: 3226

Answers (1)

Nicholas Post
Nicholas Post

Reputation: 1857

Checkout the DatePart function in SQL. It will break up and group dates based on the interval supplied (ie. day, month, year, etc.)

Sample: 

SELECT
    DatePart(year,HireDate)
    ,Count(emplid)
    ,AVG(salary)
FROM Employee
WHERE DATEDIFF(YEAR, HireDate, GetDate) <= 4
GROUP BY DatePart(year,HireDate) 
ORDER BY DatePart(year,HireDate) asc

Upvotes: 2

Related Questions