MySQL query for count on a date -

MySQL query for count on a date -

i trying work out query next statement:

get total number of pets on given day each of locations. include ones have left on day. date each booking start , end date.

i don't have database of info yet, here rough thought of table like:

| pet | arrivedate | leavedate | location | +-----+------------+------------+----------+ | 1 | 2014-11-01 | 2014-11-19 | cat pen | +-----+------------+------------+----------+ | 2 | 2014-11-02 | 2014-11-17 | dog pen | +-----+------------+------------+----------+ | 3 | 2014-11-05 | 2014-11-10 | cat pen | +-----+------------+------------+----------+ | 4 | 2014-11-06 | 2014-11-19 | cat pen | +-----+------------+------------+----------+ | 5 | 2014-11-08 | 2014-11-26 | dog pen | +-----+------------+------------+----------+ | 6 | 2014-11-14 | 2014-11-16 | cat pen | +-----+------------+------------+----------+

for '2014-11-08' output be:

| date | cat pens | dog pens | +------------+----------+----------+ | 2014-08-11 | 3 | 2 |

well, can start writing query select rows want, before preforming actions. want pets have arrived before or on date, , have not left yet. can filter in clause, this:

select * mytable arrivedate <= '2014-11-08' , leavedate >= '2014-11-08';

then, can utilize sum() aggregate function case statement number each location. this:

select '2014-11-08' date, sum(case when location = 'cat pen' 1 else 0 end) catpens, sum(case when location = 'dog pen' 1 else 0 end) dogpens mytable arrivedate <= '2014-11-08' , leavedate >= '2014-11-08';

here sql fiddle example.

mysql