CODEWITHSUNDEEP
mysql
Sharath
Sharath

Reputation: 2428

Handling MySql Group By

Below is the mysql code

CREATE TABLE pricing
( 
    `id` INT NOT NULL AUTO_INCREMENT, `cost` FLOAT NOT NULL, 
    `valid_on` TIMESTAMP NOT NULL, `quantity` INT NOT NULL, 
     PRIMARY KEY (`id`)
);

INSERT INTO pricing (`id`, `cost`, `valid_on`, `quantity`) VALUES 
(NULL, '4', '2017-01-01 00:00:00', '1'), 
(NULL, '4', '2017-01-02 00:00:00', '1'),
(NULL, '4', '2017-01-03 00:00:00', '1'),
(NULL, '5', '2017-01-04 00:00:00', '2'),
(NULL, '5', '2017-01-05 00:00:00', '2'),
(NULL, '4', '2017-01-06 00:00:00', '2'),
(NULL, '4', '2017-01-07 00:00:00', '3'),
(NULL, '5', '2017-01-08 00:00:00', '3'),
(NULL, '5', '2017-01-09 00:00:00', '3'),
(NULL, '4', '2017-01-10 00:00:00', '3'),
(NULL, '4', '2017-01-11 00:00:00', '3'),
(NULL, '4', '2017-01-12 00:00:00', '2'),
(NULL, '5', '2017-01-13 00:00:00', '2'),
(NULL, '5', '2017-01-14 00:00:00', '2');

So when Group By is done on quantity following results are displayed.

select quantity, sum(cost) from pricing GROUP BY quantity

1 - 12

2 - 28

3 - 22

But actually I need something like the below results.

 quantity     start_date             end_date          cost

    1     2017-01-01 00:00:00    2017-01-03 00:00:00    12

    2     2017-01-04 00:00:00    2017-01-06 00:00:00    14

    3     2017-01-07 00:00:00    2017-01-11 00:00:00    22

    2     2017-01-12 00:00:00    2017-01-14 00:00:00    14

SQL Fiddle Link

Can someone please help me solve this issue...

Upvotes: 1

Views: 45

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72195

Try this:

SELECT quantity, 
       MIN(valid_on) AS start_date, MAX(valid_on) AS end_date,
       SUM(cost)
FROM (
   SELECT id, cost, valid_on, quantity,
          @rn := @rn + 1 AS rn,
          @grn := IF(@q = quantity, @grn + 1,
                     IF(@q := quantity, 1, 1)) AS grp
   FROM pricing
   CROSS JOIN (SELECT @rn := 0, @q := 0, @grn := 0) AS vars
   ORDER BY valid_on, quantity) AS t
GROUP BY rn - grp, quantity

The query uses variables in order to identify islands of consecutive records having the same quantity value. Using the computed grp value, it groups separately each island and calculates start/end dates, as well as the sum of cost.

Demo here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This is a pain to do in MySQL. You need to identify the groups. One method -- which is not particularly efficient -- uses a trick. For each row it counts the number of previous rows where the quantity is different from the given row. This identifies adjacent groups with the same value.

select quantity, sum(cost), min(valid_on) as start_valid_on
from (select p.*,
             (select count(*)
              from pricing p2
              where p2.valid_on < p.valid_on and p2.quantity <> p.quantity
             ) as grp
      from pricing p
     ) p
group by grp, quantity;

Upvotes: 0

Related Questions