CODEWITHSUNDEEP
mysqlsql
Jeff Beagley
Jeff Beagley

Reputation: 1025

How to group by an interval of days

I've got a list of rows with a date format and a coinciding price ( just an example )

date     - price
01/01/14 - $5
01/02/14 - $5
01/03/14 - $1
01/10/14 - $1
01/12/14 - $2

I can successfully do a group by date with the following date alias

week(STR_TO_DATE(`date`, '%m/%e/%Y')) AS `date`,

and get it to group by week according to MySQL.

What I need is to group by an interval of days, which will be defined from a user interface, ie 1,2,3,4,5,6 days.

My expected output would be

    SELECT
     `date`,
     SUM`price`,
    FROM `table`
    GROUP BY date range of 3 days

date - price
01/01/14 - 01/03/14 - $11
01/10/14 - 01/12/14 - $3

PROPOSED IDEA

EDIT - This will not work due to creating a new column for each instance. See Proposed idea #2

    WEEKOFYEAR(STR_TO_DATE(`ship date`, '%m/%e/%Y')) AS `woy`,
    WEEKDAY(STR_TO_DATE(`ship date`, '%m/%e/%Y')) AS `wday`,

        IF ((SELECT `wday`)='0','range1', '') AS `range`,
        IF ((SELECT `wday`)='1','range1', '') AS `range`,
        IF ((SELECT `wday`)='2','range1', '') AS `range`,
        IF ((SELECT `wday`)='3','range2', '') AS `range`,
        IF ((SELECT `wday`)='4','range2', '') AS `range`,
        IF ((SELECT `wday`)='5','range2', '') AS `range`,
        IF ((SELECT `wday`)='6','range2', '') AS `range`,

PROPOSED IDEA #2

    `Ship Date`,
    WEEKOFYEAR(STR_TO_DATE(`ship date`, '%m/%e/%Y')) AS `woy`,
    WEEKDAY(STR_TO_DATE(`ship date`, '%m/%e/%Y')) AS `wday`,

        CASE
            WHEN (SELECT `wday`) = 0 THEN 'range1'
            WHEN (SELECT `wday`) = 1 THEN 'range1'
            WHEN (SELECT `wday`) = 2 THEN 'range1'
            WHEN (SELECT `wday`) = 3 THEN 'range2'
            WHEN (SELECT `wday`) = 4 THEN 'range2'
            WHEN (SELECT `wday`) = 5 THEN 'range3'
            WHEN (SELECT `wday`) = 6 THEN 'range3'
        ELSE 'Other'
            END AS `range`,

Upvotes: 0

Views: 179

Answers (1)

mixmastered
mixmastered

Reputation: 287

Create an extra column defining which "grouping" it should belong to (see eg In SQL, how can you "group by" in ranges?), perhaps using a case statement depending on what logic you need (see for eg How does MySQL CASE work?)

SELECT GROUPING, SUM(PRICE) FROM 
    (SELECT
         DATE,
         PRICE,
{CASE statement for grouping logic} GROUPING
        FROM table)
        GROUP BY grouping

Upvotes: 1

Related Questions