How to use group by for a range of date in SQL?

By arunraj

Use the following query to used group by clause to select a range of date from db using SQL.

 SELECT
 CASE WHEN age < 18 THEN  '<18'
WHEN (age > 18 AND age < 30) THEN  '>18 and <30'
WHEN (age > 30 AND age < 40) THEN  '>30 and < 40'
 WHEN (age > 40 AND age < 50) THEN  '>40 and <50'
 ELSE '>60' END  AS agelimit , COUNT(*)
FROM   EMPLOYEE
GROUP BY
CASE WHEN age < 18 THEN  '<18'
WHEN (age > 18 AND age < 30) THEN  '>18 and <30'
WHEN (age > 30 AND age < 40) THEN  '>30 and < 40'
 WHEN (age > 40 AND age < 50) THEN  '>40 and <50'
 ELSE '>60' END

The above query is used to count the number of employees in the given age limit.
The same can be achieved using the below query with out using group by.

SELECT
 SUM ( CASE WHEN age < 18 THEN 1 ELSE 0 END ) AS "<18",
SUM(CASE WHEN (age > 18 AND status_Id < 30) THEN 1 ELSE 0 END ) AS ">18 and <30",
SUM(CASE WHEN (age > 30 AND status_Id < 40) THEN 1 ELSE 0 END ) AS ">30 and < 40" ,
SUM (CASE WHEN (age > 40 AND status_Id < 50) THEN 1 ELSE 0 END ) AS ">40 and <50" ,
SUM (CASE WHEN (age > 60) THEN 1 ELSE 0 END ) AS ">60"
FROM   EMPLOYEE
  • 1228 reads

Search