CODEWITHSUNDEEP
mysqlsql
user6205450
user6205450

Reputation:

How to print between multiple dates in SQL?

i'm trying to run mysql query for average vacation hours and sick leaves for the employees that were born

• Between the years 1960 and 1969

• Between the years 1970 and 1979

• Between the years 1980 and 1989

SELECT AVG(VacationHours), AVG(SickLeaveHours)
FROM HumanResources.employee
WHERE BirthDate BETWEEN '1960-1-1' AND '1969-12-31' 
  OR  BirthDate BETWEEN '1970-1-1' AND '1979-12-31' 
  OR  BirthDate BETWEEN '1980-1-1' AND '1980-12-31'


Output 
44 49

I want output for individual decade. something like this.

Output
44 49
44 43
42 47

Upvotes: 1

Views: 508

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

You can simplify like this when the years are continuous 

SELECT Extract(YEAR FROM BirthDate) ,
       Avg(VacationHours),
       Avg(SickLeaveHours)
FROM   HumanResources.employee
WHERE  BirthDate BETWEEN '1960-1-1' AND '1980-12-31'
GROUP BY Extract(YEAR FROM BirthDate)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use GROUP BY with CASE:

SELECT (CASE WHEN BirthDate BETWEEN '1960-01-01' AND '1969-12-31' THEN '1960s'
             WHEN BirthDate BETWEEN '1970-01-01' AND '1979-12-31' THEN '1970s'
             WHEN BirthDate BETWEEN '1980-01-01' AND '1980-12-31' THEN '1980s'
        END) as decade,
       AVG(VacationHours), AVG(SickLeaveHours)
FROM HumanResources.employee
WHERE BirthDate >= '1960-01-01' AND BirthDate < '1990-01-01'
GROUP BY decade
ORDER BY decade;

MySQL permits you to use a column alias for GROUP BY. Some databases require that you repeat the expression.

Upvotes: 1

Related Questions