MYSQL group stats by range of two dates for statistics -

i try implement statistics in app.

actually wqnt display "sales" (number of orders , total amount) range of 2 dates.

i use following query :

select count(order.id), sum(order.total_amount) `order` payed_at between "2015-07-01 00:00:00" , "2015-08-25 00:00:00" 

it works range. zoom like group "all days of range", "all monthes of range"

eg days of range :

• |2015-07-01 | 0 | 0 |
• |2015-07-02 | 1 | 3000 |
• ...
• |2015-08-20 | 3 | 450 |

eg months of range :

• |2015-07-01 | 40 | 15200 |
• |2015-08-01 | 23 | 3890 |

is possible perform same query, adding "group by" ?

i know lot of different way go result want... 1 time, clean thing mysql, little bit "professional".

nice day

you funky union all i'd keep 3 queries separate.

you'll want application code render results table or graph of sorts anyway , i'd take overhead of 3 calls.

i'd run queries follows:

  select count(order.id),           sum(order.total_amount)      `order`     payed_at >= :start_date       , payed_at <  :end_date + interval 1 day    select date(payed_at) payed_date          count(order.id),           sum(order.total_amount)      `order`     payed_at >= :start_date       , payed_at <  :end_date + interval 1 day group payed_date    select extract(year_month payed_at) payed_year_month          count(order.id),           sum(order.total_amount)      `order`     payed_at >= :start_date       , payed_at <  :end_date + interval 1 day group payed_year_month 

i prefer exact logic of >=/< when querying date ranges on inclusive between.

when grouping month important remember include year if range can span year. alternatives extract include last_day , date_format.. see mysql date , time functions.

another option run 2nd query (grouped date) , roll-up month , range totals in application code.