CODEWITHSUNDEEP
phpmysqlsql
Lawrence Cooke
Lawrence Cooke

Reputation: 1597

MYSQL Groupby based on multiple fields

I have a rather complex query I need some help with.

basically my table has four fields:

Start Date,
End Date, 
Item, 
Type

I also need to get the number of days

In order to group them correctly, I need to look at each row, and check if the dates in Start Date Are consecutive, if they are group them, if not it goes into a different group

So if the following dates were in the database:

2013-10-23
2013-10-24
2013-10-28
2013-10-29

Then it should return two rows

2(number of days), 2013-10-23 (startdate) ,2013-10-24 (last consecutive date) , Item, Type
2,2013-10-28,2013-10-29,Item,Type

Then to take things to a more complex level, the grouping needs to be based on the Item and Type also being the same

So that is the following data (StartDate,Item,Type)

2013-10-23,ABC,EFG
2013-10-24,XYZ,WXY
2013-10-28,ABC,EFG
2013-10-29,ABC,EFG

Then the first two would not be grouped because the Item and/or Type are not the same as each other, even though the dates are consecutive.

However the last two would group together because the dates are consecutive and the Item and Type are the same as each other, with wanted result:

number of days  startdate   last consecutive date  Item  Type
   1            2013-10-23  2013-10-23             ABC   EFG
   1            2013-10-24  2013-10-24             XYZ   WXY
   2            2013-10-28  2013-10-29             ABC   EFG

I am trying to do this with MySQL, I know I can loop through with the aid of PHP, but it is preferable to do it in MySQL if it is at all possible.

Here is a SQLFiddle with some data in it. http://sqlfiddle.com/#!2/63383/1/0

Upvotes: 0

Views: 233

Answers (3)

Lawrence Cooke
Lawrence Cooke

Reputation: 1597

SELECT DATEDIFF(max(enddate),min(startdate)) + 1 as days, MIN(startdate) as start, MAX(enddate) as end, type, item FROM (
  SELECT   startdate, enddate,item, type,
       @group     := @group + 1 - (
                       type      <=> @last_type
                   AND item      <=> @last_item
                   AND startdate <=> @last_date + INTERVAL 1 DAY
                     ) g,
       @last_type := type,
       @last_item := item,
       @last_date := enddate
  FROM     productinfo, (
         SELECT @group     := 0,
                @last_type := NULL,
                @last_item := NULL,
                @last_date := NULL
       ) init
  ORDER BY type, item, startdate
) t GROUP BY g order by start

Upvotes: 0

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Try this:

SELECT
    DATEDIFF(MAX(startdate), MIN(startdate)) + 1 AS number_of_days,
    MIN(startdate) AS startdate,
    MAX(startdate) AS last_consecutive_date,
    item,
    type
  FROM (
    SELECT @rownum := @rownum + 1 AS row_number,
           pi.*
    FROM
      productinfo pi,
      (SELECT @rownum := 0) r
  ) mydata
GROUP BY item, type, DATE_ADD(startdate, INTERVAL -row_number DAY)
ORDER BY item, type, DATE_ADD(startdate, INTERVAL -row_number DAY)
;

Based on the answer by Andriy M in this thread.

SQLFiddle: SQLFiddle example

Upvotes: 0

eggyal
eggyal

Reputation: 125865

SELECT COUNT(*), MIN(startdate), MAX(startdate), item, type FROM (
  SELECT   startdate, item, type,
           @group     := @group + 1 - (
                           type      <=> @last_type
                       AND item      <=> @last_item
                       AND startdate <=> @last_date + INTERVAL 1 DAY
                         ) g,
           @last_type := type,
           @last_item := item,
           @last_date := startdate
  FROM     productinfo, (
             SELECT @group     := 0,
                    @last_type := NULL,
                    @last_item := NULL,
                    @last_date := NULL
           ) init
  ORDER BY type, item, startdate
) t GROUP BY g

See it on sqlfiddle.

Upvotes: 2

Related Questions