kodcu kodcu - 13 days ago 5
SQL Question

Count Age With Distinctly in MySQL

I have a table like this

PersonID Gender Age CreatedDate
================================
1 M 32 10/09/2011
2 F 33 10/09/2011
2 F 33 10/11/2011
1 M 32 10/11/2011
3 F 33 10/11/2011


I want to find Gender Count By Age with group by created date,The age range will be 30-34 and getting person will be distinctly.

Desired output should like this:

Gender AgeRange CreatedDate CountResult
================================
M 30_34 10/09/2011 1
F 30_34 10/09/2011 1
F 30_34 10/11/2011 1


So I tried this but couldtn help:

SELECT t.Gender,'30_34' AS AgeRange,t.CreatedDate,
SUM(CASE WHEN t.Age BETWEEN 30 AND 34 THEN 1 ELSE 0 END) AS CountResult,
FROM (
SELECT DISTINCT PersonID,Gender,Age,CreatedDate
FROM MyTable
GROUP PersonID,Gender,Age,CreatedDate
HAVING COUNT(PersonID)=1
) t


What can I do for solution?

Thanks

Answer

If you are want the earliest created date per personid this might do drop table if exists mytable;

create table mytable(PersonID int,  Gender varchar(1),Age int, CreatedDate date);
insert into mytable values 
(1 ,        'M',      32 , '2011-09-10'),
(2 ,        'F',      33 , '2011-09-10'),
(2 ,        'F',      33 , '2011-11-10'),
(1 ,        'M',      32 , '2011-11-10'),
(3 ,        'F',      33 , '2011-11-10');


select mt.gender,

        mt.createddate,
        sum(case when mt.age between 32 and 34 then 1 else 0 end) as Age32to34

from  mytable mt
where createddate = (select min(mt1.createddate) from mytable mt1 where mt1.personid = mt.personid)  
group by gender,mt.createddate