Q:

Select by age and gender

I made the following query to select persons by age group, as count and as percentage. Ages are stored as 0000-00-00 in my database.
SELECT  AgeGroup, count(*) AS count, ROUND(sum( 100 ) / total) AS percentage
        FROM    (
        SELECT  case
                when  age between 0 and 17 then '00 - 17'
                when  age between 18 and 24 then '18 − 24'
                when  age between 25 and 34 then '25 − 34'
                when  age between 35 and 44 then '35 − 44'
                when  age between 45 and 54 then '45 − 54'
                when  age between 55 and 64 then '55 − 64'
                when  age between 65 and 125 then '65+'
                else 'Unknown'
                end AS AgeGroup
        FROM    (
                SELECT  ROUND(DATEDIFF(Cast(NOW() as Date),
                            Cast(dateofbirth as Date)) / 365, 0) as age
                FROM    people
                ) as SubQueryAlias
        ) as SubQueryAlias2
        CROSS JOIN (SELECT count( * ) AS total FROM people)x
        group by
        AgeGroup

The current result is:
AgeGroup | count | percentage
00 - 17    33      1
18 − 24    235     5
..         ..      ..

What I need is a addition to the query to separate the results in male/female/unknown:
AgeGroup  | gender | count | percentage
00 - 17     M        33      1
00 - 17     F        33      1
..          ..       ..      ..

A:

I really hope I am wrong about it ....but would the reason of constant error be...you didn't select the gender?
Also, a nerdy side note, 365 days doesn't make a year, it's roughly 365.25 days XD which mean your equation is slightly off haha
SELECT  AgeGroup, gender, count(*) AS count, ROUND(sum( 100 ) / total) AS percentage
    FROM    (
    SELECT  case
            when  age between 0 and 17 then '00 - 17'
            when  age between 18 and 24 then '18 − 24'
            when  age between 25 and 34 then '25 − 34'
            when  age between 35 and 44 then '35 − 44'
            when  age between 45 and 54 then '45 − 54'
            when  age between 55 and 64 then '55 − 64'
            when  age between 65 and 125 then '65+'
            else 'Unknown'
            end AS AgeGroup, gender
    FROM    (
            SELECT  ROUND(DATEDIFF(Cast(NOW() as Date),
                        Cast(dateofbirth as Date)) / 365, 0) as age, 
                        gender
            FROM    people
            ) as SubQueryAlias
    ) as SubQueryAlias2
    CROSS JOIN (SELECT count( * ) AS total FROM people)x
    group by
    AgeGroup, gender

