Mysql count and group between specific dates

Question

I have a problem with a query.

I have a table like this: tbl_people

id | startDate  | endDate    | Gender
1  | 2010-03-01 | 2011-04-11 | m
2  | 2010-04-01 | 2010-06-14 | f
3  | 2010-03-01 | 2010-04-11 | m
4  | 2009-09-09 | 2009-10-10 | f

For all years given in the database I want to count the gender of the people, for who that year is between startDate and endDate. When startDate is 2010 and endDate 2011 it should count for both.

So the result should look like this:

year | m | f | m+f
2009 | 0 | 1 | 1
2010 | 2 | 1 | 3
2011 | 1 | 0 | 1

I have no really good idea how to realize that query for a list of all years. Currently I have this:

select
    sum(case tbl_people.Gender when 'm' then 1 else 0 end),
    sum(case tbl_people.Gender when 'f' then 1 else 0 end),
    count( tbl_people.Gender )
...

Best regards

Barmar · Accepted Answer · 2015-03-12 13:38:10Z

2

You need to join with a table that contains all the years. You can either create a real table with all the years, or construct it on the fly in a subquery:

SELECT y.year, 
        SUM(p.Gender = 'm') AS m,
        SUM(p.Gender = 'f') AS f,
        COUNT(*) AS `m+f`
FROM (SELECT 2009 AS year
      UNION
      SELECT 2010 AS year
      UNION
      SELECT 2011 AS year) AS y
LEFT JOIN tbl_people AS p ON y.year BETWEEN YEAR(p.startDate) AND YEAR(p.endDate)
GROUP BY y.year

DEMO

answered Mar 12, 2015 at 13:38

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql count and group between specific dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related