0

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`,
Jeff Beagley
  • 945
  • 9
  • 19

1 Answers1

1

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
Community
  • 1
  • 1
mixmastered
  • 277
  • 1
  • 3
  • I could see this working, but for dayofyear I'd have to define a case statement for each interval of grouping wouldn't I? ie 1-3, 4-7-8-10, etc. – Jeff Beagley Feb 26 '15 at 16:11
  • Maybe you need to generate a table to help, where you define the groupings you want to use? Also, analytic functions may help too: http://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/ – mixmastered Feb 27 '15 at 10:29
  • Believe that's what I'm going to end up doing, or perhaps a case situation basically saying if date is this day of the week, group by day of the week, and range. – Jeff Beagley Mar 04 '15 at 22:05
  • Added a proposed idea. Grab week of year #, and week day #.. then define a range based upon week day # to allow me to group by week #, AND day of week #. – Jeff Beagley Mar 05 '15 at 14:58
  • Nvm that won't work without nesting the IFs, perhaps a Case would be much better. – Jeff Beagley Mar 05 '15 at 15:02