Charles_Edward Charles_Edward - 1 year ago 57
MySQL Question

Mysql group by 1-15 and 16-30/31 days of a month

I have a table with a lot of rows and i need to get the count of them grouped by ID and date bimonthly

For example

**ID Date**
15 2016/01/01
15 2016/01/04
15 2016/01/05
15 2016/01/22
15 2016/01/30
15 2016/02/01
15 2016/02/16
15 2016/03/01
15 2016/03/16
15 2016/03/22

Expected results:

**Count ID Date**
3 15 2016/01/01
2 15 2016/01/15
1 15 2016/02/01
1 15 2016/02/15
1 15 2016/03/01
2 15 2016/03/15

Currently i have this:

SELECT count(*) as '#', ID, from_unixtime(Date, '%Y-%m-%d') as 'Date'
FROM table

Which indeed groups by week but starting from the first input and so on (whici is not what i want but is as close as i have gotten)

EDIT: Changed the term to bimonthly

Answer Source

The 1st thru 15th and 16th thru 30/31 groups are not "biweekly". That grouping would be referred to as bimonthly. (I'd prefer the term "dimonthly" if that were a word.)

It's odd that you would want to return a value of the 15th, for the group that would not contain the 15th, rather than returning the 16th.

You could use expressions to get you the year and month, and then the 1st or 15th.

 GROUP BY CONCAT(DATE_FORMAT(,'%Y-%m-'),IF(DAY(<16,'01','15'))
          + INTERVAL 0 DAY

You could use the same expression in the SELECT list to return the date value.

(My personal preference would be to return a date value of yyyy-mm-16, rather than -15.)

Note that this won't return a "zero count" for a bimonthly period where there aren't any rows. A row for such a period would be "missing".

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download