mysql - Create statistic of table, use alias in case -


i have table holds birthdays , genders

select `tblresultdatetime`, `tblresultbirthdate`, `tblgendertexten`   `ci_wizard_results`  inner join ci_wizard_genders on ci_wizard_results.tblresultgender = ci_wizard_genders.tblgenderid

returns:
enter image description here

now want create table this: enter image description here


so want create table points out age groups etc.
i believe first have convert dates ages:

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


but after that, not sure how continue. believe should use case:

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


but can't use alias in case, i'm kinda stuck. suggestion how continue?

(my final goal display data in report via reportico)

thanks!

assuming age calculate using 

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

you can use case when , group eg 

select          case when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 0 , 30 '0 - 30'              when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 31 , 40 '31 - 40'              when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 41 , 50 '41 - 50'                           when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 51 , 60 '51 - 60'                 when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 61 , 70 '61 - 70'                else   '70+' group end,          sum(case when  `tblgendertexten`  = 'man'  1 else  0 end)  man,              sum(case when  `tblgendertexten`  = 'woman'  1 else  0 end)  woman,                sum(1)  total `ci_wizard_results` inner join ci_wizard_genders on ci_wizard_results.tblresultgender = ci_wizard_genders.tblgenderid group    case when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 0 , 30 '0 - 30'              when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 31 , 40 '31 - 40'              when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 41 , 50 '41 - 50'                           when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 51 , 60 '51 - 60'                 when   year(`tblresultdatetime`)-year(`tblresultbirthdate`) between 61 , 70 '61 - 70'                else   '70+' group end union  select          'total ',          sum(case when  `tblgendertexten`  = 'man'  1 else  0 end)  man,              sum(case when  `tblgendertexten`  = 'woman'  1 else  0 end)  woman,                sum(1)  total `ci_wizard_results` inner join ci_wizard_genders on ci_wizard_results.tblresultgender = ci_wizard_genders.tblgenderid

-

Comments

Post a Comment

Popular posts from this blog

java - SSE Emitter : Manage timeouts and complete() -

i writing web app multiple listeners(evcentsource sse clients js) connected server . want store sse emitter per connected listener : can done in memory or other means assigning id each client able achieve far now question ; how send response/event specific client connected web app ? while doing sseemmiters stored either getting completed or timed out. how prevent ? how keep sseemmiter available open infinite time (till client closes) , send events selectively .
Read more

jquery - uncaught exception: DataTables Editor - remote hosting of code not allowed -

i'm trying implement crud operation using datatableseditor using datatables.but i'm getting error messages: 1)uncaught exception: datatables editor - remote hosting of code not allowed. please see http://editor.datatables.net details on how purchase editor license 2)typeerror: a.editor undefined 3) $.fn.datatable.editor not constructor" what reason? here configuration: <%@ page language="java" contenttype="text/html; charset=iso-8859-1" pageencoding="iso-8859-1"%> <!doctype html public "-//w3c//dtd html 4.01 transitional//en" "http://www.w3.org/tr/html4/loose.dtd"> <html> <head> <meta http-equiv="content-type" content="text/html; charset=iso-8859-1"> <title>insert title here</title> <link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.datatables.min.css"> <link re...
Read more

java - How to resolve error - package com.squareup.okhttp3 doesn't exist? -

i trying convert json java object use of gson in maven, following youtube video guidance - https://www.youtube.com/watch?v=vqgghm9pwe0 , theres error occurs when in main class error - package com.squareup.okhttp3 doesn't exist. code below: java package com.codebeasty.json; import com.squareup.okhttp3.okhttpclient; public class main { private static okhttpclient client = new okhttpclient(); public static void main (string [] args) { } } i put in dependency in pom.xml: <dependencies> <dependency> <groupid>com.squareup.okhttp3</groupid> <artifactid>okhttp</artifactid> <version>3.4.2</version> </dependency> <dependency> <groupid>com.google.code.gson</groupid> <artifactid>gson</artifactid> <version>2.8.0</version> </dependency> </dependencies> i dont understand why doesn't recognize com.squareup. there may need download? have downl...
Read more