TAGS :Viewed: 21 - Published at: a few seconds ago

[ Find sum across varying no of columns ]

I have table such that :

value1   value2     value3    value4    value5   constant
1          2          3           4         5     2
8          2          8           3         5     2
1          5          3           4         5     3
1          2          6           4         5     3

Now what i want to do is ,i want to find the sum across the columns for only those number of columns as the value given in constant field:

for example in row1 if the constant value is 2, i need to find the sum of value1+value2.

If the value in constant was 3, i need to find the sum value 1+ value2 + value3

Sorry for the bad english.What is suitable way to do it? i have been googling it for a while ow but couldn't find of a suitable way

Answer 1


value column is fixed from 1 to 5? if not you need to generate dynamic query.

try this:

SELECT
  IF(constant = 1, value1, 
    IF (constant = 2, value1 + value2,
      IF (constant = 3, value1 + value2 + value3,
        IF (constant = 4, value1 + value2 + value3 + value4, value1 + value2 + value3 + value4 + value5)
      )
    )
  )
FROM tab;

UPDATED

if i were you, i'll design like this.

tbl1(id, constant),
value_tbl1(tbl1_id, column_seq, value);

SELECT SUM(value)
FROM tbl1 t, value_tbl1 v
WHERE t.id = v.tbl1_id
  AND column_seq BETWEEN 1 AND tbl1.constant

Answer 2


Intro

The normal way to resolve this question is: chose correct structure. If you have 24 fields and you need to loop dynamically in SQL, then something went wrong. Also, it is bad that your table has not any primary key (or you've not mentioned that).

Extremely important note

It is no matter that the way I'll describe will work. It is still bad practice because of using some special things in MySQL. You can use it on your own risk - and, again, reconsider your structure if it's possible.

The hack

Actually, you can do some tricks using MySQL INFORMATION_SCHEMA tables. With this you can create "text" SQL, which later can be used in prepared statement.

My table

It's called test. Here it is:

+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| value1   | int(11) | YES  |     | NULL    |       |
| value2   | int(11) | YES  |     | NULL    |       |
| value3   | int(11) | YES  |     | NULL    |       |
| value4   | int(11) | YES  |     | NULL    |       |
| constant | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+

-I have 4 "value" fields in it and no primary key column (that causes troubles, but I've resolved that). Now, my data:

+--------+--------+--------+--------+----------+
| value1 | value2 | value3 | value4 | constant |
+--------+--------+--------+--------+----------+
|      2 |      5 |      6 |      0 |        2 |
|      1 |   -100 |      0 |      0 |        1 |
|      3 |     10 |    -10 |      0 |        3 |
|      4 |      0 |     -1 |      5 |        3 |
|     -1 |      1 |     -1 |      1 |        4 |
+--------+--------+--------+--------+----------+

The trick

It's about selecting data from mentioned service schema in MySQL and working with GROUP_CONCAT function:

select 
  concat('SELECT CASE(seq) ', 
    group_concat(groupcase separator ''), 
    ' END AS result FROM (select *, @j:=@j+1 as seq from test cross join (select @j:=0) as initj) as inittest') 
from 
  (select 
    concat(' WHEN ', rownum, ' THEN ', groupvalue) as groupcase 
   from 
     (select 
       rownum, 
       group_concat(COLUMN_NAME SEPARATOR '+') as groupvalue 
      from 
       (select 
         *, 
         @row:=@row+1 as rownum 
        from test 
          cross join (select @row:=0) as initrow) as tablestruct 
        left join 
          (select 
             COLUMN_NAME, 
             @num:=@num+1 as num 
           from 
             INFORMATION_SCHEMA.COLUMNS cross join (select @num:=0) as init 
           where 
             TABLE_SCHEMA='test' && 
             TABLE_NAME='test' && 
             COLUMN_NAME!='constant') as struct 
          on tablestruct.constant>=struct.num 
        group by 
          rownum) as groupvalues) as groupscase

-what will this do? Actually, I recommend to execute it step-by-step (i.e. add more complex layer to that which you've already understood) - I doubt there's short way to describe what's happening. It's not a wizardry, it's about constructing valid text SQL from input conditions. End result will be like:

SELECT CASE(seq)  WHEN 1 THEN value1+value2 WHEN 2 THEN value1 WHEN 3 THEN value3+value2+value1 WHEN 4 THEN value3+value2+value1 WHEN 5 THEN value2+value1+value4+value3 END AS result FROM (select *, @j:=@j+1 as seq from test cross join (select @j:=0) as initj) as inittest

(I didn't add formatting because that SQL is generated string, not the one you'll write by yourself).

Last step

What now? Just Allocate it with:

mysql> set @s=(select concat('SELECT CASE(seq) ', group_concat(groupcase separator ''), ' END AS result FROM (select *, @j:=@j+1 as seq from test cross join (select @j:=0) as initj) as inittest') from (select concat(' WHEN ', rownum, ' THEN ', groupvalue) as groupcase from (select rownum, group_concat(COLUMN_NAME SEPARATOR '+') as groupvalue from (select *, @row:=@row+1 as rownum from test cross join (select @row:=0) as initrow) as tablestruct left join (select COLUMN_NAME, @num:=@num+1 as num from INFORMATION_SCHEMA.COLUMNS cross join (select @num:=0) as init where TABLE_SCHEMA='test' && TABLE_NAME='test' and COLUMN_NAME!='constant') as struct on tablestruct.constant>=struct.num group by rownum) as groupvalues) as groupscase);
Query OK, 0 rows affected (0.00 sec)

mysql> prepare stmt from @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

-and, finally:

mysql> execute stmt;

You'll get results as:

+--------+
| result |
+--------+
|      7 |
|      1 |
|      3 |
|      3 |
|      0 |
+--------+

Why is this bad

Because it generates string for whole table. I.e. for each row! Imagine if you'll have 1000 rows - that will be nasty. MySQL also has limitation in GROUP_CONCAT: group_concat_max_len - which will limit this way, obviously.

So why I did that?

Because I was curious if the way without additional DDL and implicit recounting of table's fields exist. I've found it, so leaving it here.

Answer 3


The correct approach might go something like this...

DROP TABLE IF EXISTS variables;

CREATE TABLE variables
(constant_id INT NOT NULL
,sequence_id INT NOT NULL
,value INT NOT NULL
,PRIMARY KEY(constant_id,sequence_id)
);

INSERT INTO variables VALUES
(1,1,1),
(1,2,2),
(1,3,3),
(1,4,4),
(1,5,5),
(2,1,8),
(2,2,2),
(2,3,8),
(2,4,3),
(2,5,5),
(3,1,1),
(3,2,5),
(3,3,3),
(3,4,4),
(3,5,5),
(4,1,1),
(4,2,2),
(4,3,6),
(4,4,4),
(4,5,5);

DROP TABLE IF EXISTS constants;

CREATE TABLE constants
(constant_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,constant INT NOT NULL
);

INSERT INTO constants VALUES (1,2),(2,2),(3,3),(4,3);

SELECT * FROM constants;
+-------------+----------+
| constant_id | constant |
+-------------+----------+
|           1 |        2 |
|           2 |        2 |
|           3 |        3 |
|           4 |        3 |
+-------------+----------+

SELECT * FROM variables;
+-------------+-------------+-------+
| constant_id | sequence_id | value |
+-------------+-------------+-------+
|           1 |           1 |     1 |
|           1 |           2 |     2 |
|           1 |           3 |     3 |
|           1 |           4 |     4 |
|           1 |           5 |     5 |
|           2 |           1 |     8 |
|           2 |           2 |     2 |
|           2 |           3 |     8 |
|           2 |           4 |     3 |
|           2 |           5 |     5 |
|           3 |           1 |     1 |
|           3 |           2 |     5 |
|           3 |           3 |     3 |
|           3 |           4 |     4 |
|           3 |           5 |     5 |
|           4 |           1 |     1 |
|           4 |           2 |     2 |
|           4 |           3 |     6 |
|           4 |           4 |     4 |
|           4 |           5 |     5 |
+-------------+-------------+-------+

SELECT c.constant_id
     , SUM(v.value) 
  FROM constants c 
  JOIN variables v 
    ON v.constant_id = c.constant_id 
   AND v.sequence_id <= c.constant 
 GROUP 
    BY c.constant_id;
+-------------+--------------+
| constant_id | SUM(v.value) |
+-------------+--------------+
|           1 |            3 |
|           2 |           10 |
|           3 |            9 |
|           4 |            9 |
+-------------+--------------+