Mysql – Add multiples of rows till they satisfy an arbitrary sum then return the rows that do

MySQL

Am not sure if this is possible. Let's say I have an arbitrary figure like 200, I have a table with two columns (both ints). Lets call the columns col_a and col_b.

I need to perform one query such that if col_a * col_b for the first row does not equal to my arbitrary figure (in this case 200), keep whatever value the first row equates to and go to second row, do the same while adding the results to the value we got from the first row, repeat this till the total equates to our arbitrary value.

In an example. Lets use our arbitrary figure of 200 say we have row 1 (col_a*colb) = 20, row 2 (col_a*col_b) = 70, row 3 (col_a*colb) = 110, row 4 (col_a*col_b) = 30.

My query should return row 1, row 2 and row 3. How is this possible in sql? I am using mysql.

Best Answer

You could use user defined variable in mysql to achieve your goal:

create table t ( id int not null auto_increment, 
                 col_a int not null, 
                 col_b int not null, 
                 primary key(id) );

insert into t(col_a, col_b) values (1,20), (1,70), (1,110), (1,30);

set @sum := 0;
select *, col_a * col_b, @sum as s 
from t
where (@sum := @sum + (col_a * col_b)) <= 200
order by id; 

+----+-------+-------+---------------+------+
| id | col_a | col_b | col_a * col_b | s    |
+----+-------+-------+---------------+------+
|  1 |     1 |    20 |            20 |   20 |
|  2 |     1 |    70 |            70 |   90 |
|  3 |     1 |   110 |           110 |  200 |
+----+-------+-------+---------------+------+
3 rows in set (0.00 sec)

You must use order by to make sure of the rows order as suggested by ypercube. For example you can sum column in reverse order.

set @sum := 0;
select *, col_a * col_b, @sum as s  
from t where (@sum := @sum + (col_a * col_b)) <= 200 
order by id desc;

+----+-------+-------+---------------+------+
| id | col_a | col_b | col_a * col_b | s    |
+----+-------+-------+---------------+------+
|  4 |     1 |    30 |            30 |   30 |
|  3 |     1 |   110 |           110 |  140 |
+----+-------+-------+---------------+------+
2 rows in set (0.00 sec)

Related Solutions

MySQL – SELECT LIMIT 1 Per Column Value

That's what GROUP BY is used for. Get one row (per group). In this case, it will show all distinct user_id values and for the rest of the columns, you can (have to) use aggregate functions like MIN(), MAX(), AVG(), SUM() as you will have more than one values per group and only one can be shown.

SELECT
    user_id
  , MIN(comment) AS comment  -- it will show the first in alphabetical order  
                             -- you could also use MAX()
FROM
    tableX
GROUP BY
    user_id ;

MySQL allows also the following unorthodox solution, that will return one (more or less random) comment per user:

SELECT
    user_id
  , comment
FROM
    tableX
GROUP BY
    user_id ;

This last query will not work but raise an error if the (stricter) ONLY_FULL_GROUP_BY mode is enabled. In the recently released 5.7 version, this mode is the default and a new function, ANY_VALUE(), is provided. For more details, see the MySQL Handling of GROUP BY page. The query can be written now:

SELECT
    user_id
  , ANY_VALUE(comment) AS comment
FROM
    tableX
GROUP BY
    user_id ;

Note that with either the "unorthodox" version or using the recent ANY_VALUE() function, if we add more columns in the SELECT list, their values is not guaranteed to be from the same row, just from a row in the same group. The way they are selected is not exactly random, depends on the execution plan and the indexes used.

Mysql – aggregate on join latest value

As the way your schema current sits, you'll need two parts; the main select statement and (since you are using MySQL, which doesn't support common table expressions), a view in the where clause.

The view, priveView:

SELECT product_id
, MAX (valid_since) [validSince]
FROM prices
GROUP BY product_id

The select statement:

SELECT SUM (P.Prices)
FROM Prices AS P
INNER JOIN priceView as PV ON P.product_id = PV.Product_Id AND PV.valid_since = P.valid_since

If possible I would change the schema of your prices tables so that it also includes a column of when the price starts in additional when it ends/changes. Whatever the current price happens to be has it's "price_end" value set to null. You'd then be able to avoid the need for an intermediate view that gets the sub-section of rows that apply.

Best Answer

Related Solutions

MySQL – SELECT LIMIT 1 Per Column Value

Mysql – aggregate on join latest value

Related Question