SQL Query to find who are born until the year 2000-CodePudding

Two relationships are given: Employee (AngID, Name, Surname, Date of Birth, Gender, Education, Position, Monthly Salary, DepID) Department (DepID, DepName, City)

Print the SQL statement or statements that solve the following problem: To display the number of employees according to the year of birth, who are born until the year 2000. The following will be displayed: Year, Number. The tuples will be ordered in descending order of the year of birth.

I tried this:

SELECT Year, Number
FROM Employee
WHERE Date of Birth <2000 AS Num
ORDER BY Date of Birth DESC

CodePudding user response：

I assume this is the query you are after:

SELECT YEAR(DateofBirth) as Year, Count(1) AS NumberOfEmployees
FROM Employee
WHERE DateofBirth < '2000-01-01'
GROUP BY YEAR(DateofBirth)
ORDER BY YEAR(DateofBirth) DESC

This query will filter all employees who were born before year 2000. It will group the records by year and present the count.

sqlfiddle example

CodePudding user response：

To display the number of employees

COUNT(*)

FROM Employee emp

according to the year of birth

GROUP BY DATEPART(YEAR, [Date of Birth])

who are born until the year 2000.

WHERE [Date of Birth] < '2001-01-01'

The following will be displayed: Year, Number.

DATEPART(YEAR, [Date of Birth]) AS [Year], 
COUNT(*) AS [Number]

The tuples will be ordered in descending order of the year of birth.

ORDER BY [Year] DESC

SQL:

SELECT
  DATEPART(YEAR, [Date of Birth]) AS [Year] 
, COUNT(*) AS [Number] 
FROM Employee emp
WHERE [Date of Birth] < '2001-01-01' 
GROUP BY DATEPART(YEAR, [Date of Birth]) 
ORDER BY [Year] DESC