group by clause flask alchemy-CodePudding

sample = {
        '13-18':50,
        '19-25':60,
        '25-40':50,
        '50-80':100

    }

I want to find the count of users who fall in the above mentioned categories, that is from age 13-18, 19-25 respectively , how would i query using SQLalchemy , i am having only date of birth field to work with

dob : 07/10/2005

DOB is in above mentioned format

CodePudding user response：

You should add more details. What is your database (postres, mysql, other?)? Depending on DB, there are different set of builtin functions. And what the type of column dob is? Is it a string or a datetime. In postgreSQL your implementations could be:

select 
case 
    when age < 13 then '0-12'
    when age >= 13 and age < 25 then '13-25'
    when age >= 25 and age < 40 then '25-40'
    when age >= 41 and age < 80 then '41-80'
    else '80 '
end as range,
count(*)
from (
  SELECT 
  1   extract('years' from justify_interval(now() - to_date(dob, 'DD/MM/YYYY'))) as age 
  from table
) x
group by range

You can rewrite it to sqlalchemy syntax, but it is tricky. You can also use raw sql execution in sqlalchemy, like:

from sqlalchemy.sql import text

statement = text(<sql_above>)
result = session.execute(statement)