Sql Group by Age Range

SQL Group by Age Range

Try it this way instead:

 SELECT SUM(CASE WHEN age_c < 18 THEN 1 ELSE 0 END) AS [Under 18],
        SUM(CASE WHEN age_c BETWEEN 18 AND 24 THEN 1 ELSE 0 END) AS [18-24],
        SUM(CASE WHEN age_c BETWEEN 25 AND 34 THEN 1 ELSE 0 END) AS [25-34]
 FROM contacts

SQL Group patients by age range and sex

SELECT CASE
  WHEN patient.birthdate  > '01-jan-1988'  THEN '1-25' 
  WHEN patient.birthdate > '01-jan-1963' THEN '25-50'
  WHEN patient.birthdate > '01-jan-1938' THEN '50-75' 
  ELSE '75+' 
END AS Age_Range, 
SUM(CASE WHEN patient.Sex = 'M' THEN 1 ELSE 0 END) AS Males,
SUM(CASE WHEN patient.Sex = 'F' THEN 1 ELSE 0 END) AS Females
FROM patient
GROUP BY CASE
  WHEN patient.birthdate  > '01-jan-1988'  THEN '1-25' 
  WHEN patient.birthdate > '01-jan-1963' THEN '25-50'
  WHEN patient.birthdate > '01-jan-1938' THEN '50-75' 
  ELSE '75+' 
END

SQL Server: How to make count(*) of Ages groups

1. Categorize the records using case statements.
2. Group them and count

---

    Select a.AgeGroup, count(*) Count from (
select 
Case when  Age < 19 then '<19' 
     when Age between 19 and 34 then '19-34'
     when  Age > 75 then '>75' end AgeGroup 
from age ) a
group by a.AgeGroup

SQL query to group by age range from date created

Assuming that CREATEDATE is a date column, in PostgreSQL you can use the AGE function:

select DEPARTMENT, age(CREATEDATE) as AGE
from Materials

and with date_part you can get the age in years. To show the data in the format that you want, you could use this GROUP BY query:

select
  DEPARTMENT,
  sum(case when date_part('year', age(CREATEDATE))<10 then 1 end) as "age<10",
  sum(case when date_part('year', age(CREATEDATE))>=10 and date_part('year', age(CREATEDATE))<20 then 1 end) as "10<age<20",
  sum(case when date_part('year', age(CREATEDATE))>=20 then 1 end) as "20<age"
from
  Materials
group by
  DEPARTMENT

which can be simplified as:

with mat_age as (
  select DEPARTMENT, date_part('year', age(CREATEDATE)) as mage
  from Materials
)
select
  DEPARTMENT,
  sum(case when mage<10 then 1 end) as "age<10",
  sum(case when mage>=10 and mage<20 then 1 end) as "10<age<20",
  sum(case when mage>=20 then 1 end) as "20<age"
from
  mat_age
group by
  DEPARTMENT;

if you are using PostgreSQL 9.4 you can use FILTER:

with mat_age as (
  select DEPARTMENT, date_part('year', age(CREATEDATE)) as mage
  from Materials
)
select
  DEPARTMENT,
  count(*) filter (where mage<10) as "age<10",
  count(*) filter (where mage>=10 and mage<20) as "10<age<20",
  count(*) filter (where mage>=20) as "20<age"
from
  mat_age
group by
  DEPARTMENT;

Group by age ranges in MySQL

May be a query like below would do the job.

SELECT 
CASE WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birth_date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birth_date, '00-%m-%d'))) <= 20 THEN '1-20'
     WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birth_date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birth_date, '00-%m-%d'))) <= 30 THEN '20-30'
     WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birth_date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birth_date, '00-%m-%d'))) <= 50 THEN '30-50'
     WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birth_date, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(birth_date, '00-%m-%d'))) <= 50 THEN '50-100' END AS age,
COUNT(*) total
FROM dob_table
GROUP BY age;

WORKING DEMO

Input:

| id |                 birth_date |
|----|----------------------------|
|  1 | February, 01 2014 00:00:00 |
|  2 | February, 01 2014 00:00:00 |
|  3 | February, 01 2014 00:00:00 |
|  4 | February, 01 2010 00:00:00 |
|  5 | February, 27 1989 00:00:00 |
|  6 | February, 27 1989 00:00:00 |
|  7 | February, 27 1989 00:00:00 |
|  8 | February, 27 1989 00:00:00 |
|  9 | February, 27 1989 00:00:00 |

Output:

age      total
1-20        4
20-30       5

SQL display age range

You can use UNION ALL:

SELECT '[Under 10]' as Age, SUM(CASE WHEN age < 10 THEN 1 ELSE 0 END) as People
FROM people
UNION ALL
SELECT '[11-20]', SUM(CASE WHEN age BETWEEN 11 AND 20 THEN 1 ELSE 0 END)
FROM people
UNION ALL
SELECT '[21-30]', SUM(CASE WHEN age BETWEEN 21 AND 30 THEN 1 ELSE 0 END)
FROM people;

BigQuery group by specific age ranges and sex

How to get count of people based on age groups using SQL query in Oracle database?

First, your age calculation is incorrect. You almost certainly want to measure the months between the two dates rather than hoping that dividing by 365.25 is close enough

trunc( months_between( sysdate, p.birth_date )/ 12 )

Second, if you want to group by ranges, you just need to select the range in a case statement and group by that

SELECT (case when age <= 5
             then 'age <= 5'
             when age > 5 and age <= 10
             then 'age > 5 and age <= 10'
             else 'age > 10'
         end) bucket,
       count(*)
  FROM( SELECT trunc( months_between( sysdate, p.birth_date )/ 12 ) age
          FROM person p )
 GROUP BY (case when age <= 5
                then 'age <= 5'
                when age > 5 and age <= 10
                then 'age > 5 and age <= 10'
                else 'age > 10'
            end)

MySQL GROUP BY age range including null ranges

An alternative to the range table (which has my preference), a single-row answer could be:

SELECT
    SUM(IF(age < 20,1,0)) as 'Under 20',
    SUM(IF(age BETWEEN 20 and 29,1,0)) as '20 - 29',
    SUM(IF(age BETWEEN 30 and 39,1,0)) as '30 - 39',
    SUM(IF(age BETWEEN 40 and 49,1,0)) as '40 - 49',
...etc.
FROM inquiries;

---

Related Topics