Add subgroups in MySQL when a column has a certain value

Question

Add subgroups in MySQL when a column has a certain value

Navigation

#1 by (0 votes)

1

I have the following table:

| start_date               | end_date             |     sales    | is_finished |
|:------------------------:|---------------------:|:------------:|:------------:
| 2017-03-24 09:11:00      |  2017-03-24 09:11:00 |     30       |       1
| 2017-03-24 08:30:00      |  2017-03-24 08:30:00 |     50       |       1
| 2017-03-24 08:14:00      |  2017-03-24 08:14:00 |     100      |       1
| 2017-03-24 07:30:00      |  2017-03-24 07:45:00 |     200      |       0 
| 2017-03-24 07:00:00      |  2017-03-24 07:00:00 |     150      |       1 
| 2017-03-24 06:40:00      |  2017-03-24 06:50:00 |     450      |       0

I need to group by start_date and end_date and add the value of the column sales . Now, they must be grouped in the same way each time the column is_finished has a one.

That is, columns that have a zero will be added and when a one appears it should be grouped as a set.

I need the following result:

| start_date               | end_date             |   SUM(sales) |
|:------------------------:|---------------------:|:------------:|
| 2017-03-24 09:11:00      |  2017-03-24 09:11:00 |     30       |
| 2017-03-24 08:30:00      |  2017-03-24 08:30:00 |     50       |
| 2017-03-24 07:30:00      |  2017-03-24 08:14:00 |     300      |
| 2017-03-24 06:40:00      |  2017-03-24 07:00:00 |     600      |

For now I have a query but the problem is that when there are 2 or more rows that have the value 1 in the column is_finished take them as a group.

The query is:

SELECT SUM(sales) , MIN(start_date) , MAX(end_date) 
FROM sales 
GROUP BY 
start_date > (SELECT start_date FROM sales WHERE is_finished = 1  ORDER BY end_date ASC LIMIT 1 OFFSET 1 ),
 end_date <= (SELECT end_date FROM sales WHERE is_finished = 1  ORDER BY end_date ASC LIMIT 1)
ORDER BY end_date DESC

Using the query above I approach what I expect, but it has the problem mentioned.

mysql

asked by Irvin Chan 25.03.2017 в 23:18

source

1 answer

verify repeated values in datatable jQuery Make a DISTINCT only in one column?

score 0 · Answer 1

Leaving my question aside. Let's suppose that the table is as you described it and you will list the sales in chronologically descending order.

create table ventas as 
select '2017-03-24 06:40:00' as start_date, '2017-03-24 06:50:00' as end_date, 450 as sales, 0 as is_finished
UNION ALL SELECT '2017-03-24 07:00:00','2017-03-24 07:00:00', 150, 1
UNION ALL SELECT '2017-03-24 07:30:00','2017-03-24 07:45:00', 200, 0
UNION ALL SELECT '2017-03-24 08:14:00','2017-03-24 08:14:00', 100, 1
UNION ALL SELECT '2017-03-24 08:30:00','2017-03-24 08:30:00', 50 , 1
UNION ALL SELECT '2017-03-24 09:11:00','2017-03-24 09:11:00', 30 , 1;

SELECT * from VENTAS ORDER BY start_date DESC

That looks like in your original question:

| start_date               | end_date             |     sales    | is_finished |
|:------------------------:|---------------------:|:------------:|:------------:
| 2017-03-24 09:11:00      |  2017-03-24 09:11:00 |     30       |       1
| 2017-03-24 08:30:00      |  2017-03-24 08:30:00 |     50       |       1
| 2017-03-24 08:14:00      |  2017-03-24 08:14:00 |     100      |       1
| 2017-03-24 07:30:00      |  2017-03-24 07:45:00 |     200      |       0 
| 2017-03-24 07:00:00      |  2017-03-24 07:00:00 |     150      |       1 
| 2017-03-24 06:40:00      |  2017-03-24 06:50:00 |     450      |       0

What is done in MySQL is to use a variable. That variable increases when is_finished is 1 and stays the same when is_finished is 0. This means: if 1 is a new batch. If it is zero, it belongs to the lot in the previous row

   SELECT ventas.*,
          @thislote:=IF(is_finished=0,@thislote,@thislote+1) lote
   FROM ventas, (SELECT @thislote:=0) lote
   ORDER BY start_date DESC

This throws

| start_date               | end_date             |   sales  | is_finished | lote |
|:------------------------:|---------------------:|:--------:|:------------:------|
| 2017-03-24 09:11:00      |  2017-03-24 09:11:00 |   30     |       1     | 1    |
| 2017-03-24 08:30:00      |  2017-03-24 08:30:00 |   50     |       1     | 2    | 
| 2017-03-24 08:14:00      |  2017-03-24 08:14:00 |   100    |       1     | 3    |
| 2017-03-24 07:30:00      |  2017-03-24 07:45:00 |   200    |       0     | 3    |
| 2017-03-24 07:00:00      |  2017-03-24 07:00:00 |   150    |       1     | 4    |
| 2017-03-24 06:40:00      |  2017-03-24 06:50:00 |   450    |       0     | 4    |

If you convert that into a subquery, say ventas_con_lote then you just have to do:

SELECT lote, SUM(sales) , MIN(start_date) , MAX(end_date) 
FROM ventas_con_lote 
GROUP BY lote
ORDER BY end_date DESC