CODEWITHSUNDEEP
mysqlsqlselectgroup-by
Hidayet Gürses
Hidayet Gürses

Reputation: 1

MySQL :: group by between price AND two dates

I have a table like following columns:

id  date         price  pid
---------------------------
1   2015-02-01   34     2
2   2015-02-02   34     2
3   2015-02-03   34     2

4   2015-02-04   78     2
5   2015-02-05   78     2
6   2015-02-06   78     2

7   2015-02-07   52     2
8   2015-02-08   52     2
9   2015-02-09   52     2

10  2015-02-10   34     2
11  2015-02-11   34     2
12  2015-02-12   34     2

Now I want following result:

date_from    date_to      price   pid
-------------------------------------
2015-02-01   2015-02-03   34      2
2015-02-04   2015-02-06   78      2
2015-02-07   2015-02-09   52      2
2015-02-10   2015-02-12   34      2

IMPORTANT:

I don't want to group the price "34" in this case.

Upvotes: 0

Views: 994

Answers (4)

Strawberry
Strawberry

Reputation: 33945

Borrowing M Khalid Junaid's fiddle...

SELECT a.date date_from
     , MIN(c.date) date_to
     , price
     , pid
  FROM test a
  LEFT 
  JOIN test b 
    ON b.pid = a.pid
   AND b.price = a.price
   AND b.id = a.id - 1
  LEFT 
  JOIN test c 
    ON c.pid = a.pid
   AND c.price = a.price
   AND c.id >= a.id 
  LEFT 
  JOIN test d 
    ON d.pid = a.pid
   AND d.price = a.price
   AND d.id = c.id + 1
 WHERE b.id IS NULL 
   AND c.id IS NOT NULL
   AND d.id IS NULL
 GROUP 
    BY a.id, a.price, a.pid;

http://sqlfiddle.com/#!2/478f9/6

Upvotes: 1

shruti
shruti

Reputation: 707

Try this in MySQL. Here app_date is Date in your question:

set @rownum=1;
select min(app_date),max(app_date),price,pid from
(select t.id1 id,t.app_date app_date,t.price1 price,t.pid pid,
case when (t.price1=t.price2 or t.price2 is null) then @rownum else @rownum:=@rownum+1 end  temp_num                   from
(select a.id id1,b.id id2,a.app_date app_date,a.price price1,b.price price2,a.pid pid from
test a left outer join test b on a.id=b.id+1) t
order by id1) temp
group by price,pid,temp_num
order by min(app_date);

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

One solution i can think of using user defined variables also i assume the id part is set to auto_increment 

select min(t1.date) date_from,
max(t1.date) date_to,
t1.price,
t1.pid
from (
select t.*,
@r:= case when @g = price then @r  else @r + 1 end r,
@g:= price g
from test t
cross join (select @g:=null,@r:=0) t
order by id
) t1
group by t1.r

DEMO

Upvotes: 1

Abhishek Gupta
Abhishek Gupta

Reputation: 4166

Try below query, it will group by three records on basis of date and price

SELECT min(created) AS date_from, max(created) AS date_to, price, pid
FROM t1
GROUP BY price, floor(DATEDIFF("2015-02-01", created) / 3);

Upvotes: 0

Related Questions