mitch2k mitch2k - 18 days ago 5
MySQL Question

Create statistic of table, use alias in case

I have table which holds birthdays and genders

SELECT `tblresultdatetime`, `tblresultbirthdate`, `tblgendertexten`

FROM `ci_wizard_results`

INNER JOIN ci_wizard_genders ON ci_wizard_results.tblresultgender = ci_wizard_genders.tblgenderid


Returns:


enter image description here

Now I want to create a table like this:
enter image description here


So I want to create a table which points out the age groups etc.

I believe I first have to convert the dates to ages:

select *,year(`tblresultdatetime`)-year(`tblresultbirthdate`) - (right(`tblresultdatetime`,5) < right(`tblresultbirthdate`,5)) as age from `ci_wizard_results`



But after that, I am not sure how to continue. I believe I should use case:

select *,year(`tblresultdatetime`)-year(`tblresultbirthdate`) - (right(`tblresultdatetime`,5) < right(`tblresultbirthdate`,5)) as age,
count(case when age <= 30 and age> 39 then 1 end) as agegroup3039


from `ci_wizard_results`



But you can't use an alias in case, so I'm kinda stuck. Any suggestion how I could continue?



(My final goal is to display the data in a report via reportico)



Thanks!

Answer

Assuming that the age is calculate simply using

year(`tblresultdatetime`)-year(`tblresultbirthdate`)

you can use case when and group by eg

select 
        case when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 0 and 30 then '0 - 30'
             when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 31 and 40 then '31 - 40'
             when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 41 and 50 then '41 - 50'             
             when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 51 and 60 then '51 - 60'   
             when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 61 and 70 then '61 - 70'  
             else   '70+' as Group end, 
        sum(case when  `tblgendertexten`  = 'man'  then 1 else  0 end)  as man,     
        sum(case when  `tblgendertexten`  = 'woman'  then 1 else  0 end)  as woman,       
        sum(1)  as total
FROM `ci_wizard_results`
INNER JOIN ci_wizard_genders ON ci_wizard_results.tblresultgender = ci_wizard_genders.tblgenderid
group by    case when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 0 and 30 then '0 - 30'
             when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 31 and 40 then '31 - 40'
             when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 41 and 50 then '41 - 50'             
             when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 51 and 60 then '51 - 60'   
             when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 61 and 70 then '61 - 70'  
             else   '70+' as Group end
union 
select 
        'total ', 
        sum(case when  `tblgendertexten`  = 'man'  then 1 else  0 end)  as man,     
        sum(case when  `tblgendertexten`  = 'woman'  then 1 else  0 end)  as woman,       
        sum(1)  as total
FROM `ci_wizard_results`
INNER JOIN ci_wizard_genders ON ci_wizard_results.tblresultgender = ci_wizard_genders.tblgenderid
Comments