How to add several columns and get the maximum value?

Question

The below picture shows a table with 6 columns filled with numbers.

SELECT MAX(col_6) AS c1,
       MAX(col_6 + col_5) AS c2,
       MAX(col_6 + col_5 + col_4) AS c3,
       MAX(col_6 + col_5 + col_4 + col_3) AS c4,
       MAX(col_6 + col_5 + col_4 + col_3 + col_2) AS c5
INTO OUTFILE 'path/to/file.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_1;

This code is usefull in a small table, as shown below.

Unfortunately, I am not able to execute a similar code to get the maximum values in 400 columns (instead of 5 columns as shown in the example), as the code line would be too long.

So, I would like to ask your help to write a code in order to get the maximum value in a column, then add this column to the previous one and get the maximum value, and so on... up to add 400 columns and get the maximum value.

Thanks.

Update 1 (data as text). Primary key: id

id,col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9,col_10

1,10,6,19,34,49,3,20,7,2,46

2,3,21,41,39,35,25,14,36,40,11

3,44,3,15,19,21,31,8,18,30,43

4,24,17,6,46,28,18,13,8,45,5

5,39,42,22,10,37,38,20,19,23,33

Yes, it has a primary key, named "id" (not shown in the picture). — JOAO12, Apr 13 '20 at 22:36
@nbk, the actual table has a primary key (column named id) and 400 columns (tinyint) (named col_1, col_2, ... col_400) filled with numbers (1 to 50). The actual table is similar to the table shown in the example, except that it is bigger. Database version 8.0.19. I need a code to get the maximum value in c_400, then, add c_400 to c_399 and get the maximum value, then add c_400 to c_399 and c_398 and get the maximum value, and so on, until add all 400 columns (c_400 up to c_1) and get the maximum value. — JOAO12, Apr 13 '20 at 22:50
Yes but to test i would like some data, so 6 or 10 columns i don't mind, but i don't type this in- I would simply write a procedure where you all it with number of colmns and it build the query by it self as long as the colmns have a naming oattern. — nbk, Apr 13 '20 at 22:53
As I'm sure you've realised, this kind of problem is highly symptomatic of poor schema design. A database table is not a spreadsheet. — Strawberry, Apr 14 '20 at 00:53

nbk · Accepted Answer · 2020-04-14T20:51:59.560

It is quite easy

With this procedure

DROP procedure IF EXISTS `proc_add_userdata`;

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_add_userdata`(IN Number_columns INTEGER)
BEGIN
    DECLARE icount INTEGER DEFAULT Number_columns - 1;
    DECLARE addtext  LONGTEXT DEFAULT '';
    SET @sql = CONCAT('SELECT MAX(col_',Number_columns,'),');
    SET addtext = CONCAT('col_',Number_columns, ' + ');
    loop_label:  LOOP
        IF icount = 1 THEN
            SET addtext = CONCAT(addtext,'col_',icount);
            SET @sql = CONCAT(@sql,'MAX(',addtext, ") INTO OUTFILE 'path/to/file_with_results_1.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM userdata;");
            LEAVE  loop_label;
        END IF;
        SET addtext = CONCAT(addtext,'col_',icount);
        SET @sql = CONCAT(@sql,'MAX(',addtext, ' ),');
        SET addtext = CONCAT(addtext,' + ');
        SET icount = icount - 1;
    END LOOP;
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1; 
END$$

DELIMITER ;

And this call

CALL proc_add_userdata(10);

the procedure will build this query and run it

SELECT 
    MAX(col_10)
    ,MAX(col_10 + col_9 )
    ,MAX(col_10 + col_9 + col_8 )
    ,MAX(col_10 + col_9 + col_8 + col_7 )
    ,MAX(col_10 + col_9 + col_8 + col_7 + col_6 )
    ,MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 )
    ,MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 + col_4 )
    ,MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 + col_4 + col_3 )
    ,MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 + col_4 + col_3 + col_2 )
    ,MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 + col_4 + col_3 + col_2 + col_1) 
FROM userdata;

RESULT

MAX(col_10) MAX(col_10 + col_9 )    MAX(col_10 + col_9 + col_8 )    MAX(col_10 + col_9 + col_8 + col_7 )    MAX(col_10 + col_9 + col_8 + col_7 + col_6 )    MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 )    MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 + col_4 )    MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 + col_4 + col_3 )    MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 + col_4 + col_3 + col_2 )    MAX(col_10 + col_9 + col_8 + col_7 + col_6 + col_5 + col_4 + col_3 + col_2 + col_1)
46          73                     91                               101                 133 170 200 241 262 283

You get the picture.

You can improve the stored procedure, to get the number of columns be doing a query see https://stackoverflow.com/a/10492187/5193536

Alo you can add am alias for every max value buy add for the comma (and of course for icount = 1) a ' c_',icount

For debugging purposes comment the prepare and excute out ny adding -- and add the line SELECT @sql; so you get the text and can check for errors.

Thank you very much! I would like to ask you if it is possible to add this code `FROM userdata INTO OUTFILE 'path/to/file_with_results_1.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';` to your code. Thanks. — JOAO12, Apr 14 '20 at 19:28
i have hopes that you take a llittle tome to understand the procedure. I have add the the output part as you can see in the code the last part for col_1 is build when the loop stops at 1 there you also find the FROM tablename — nbk, Apr 14 '20 at 20:30
I corrected it, the query build a string by concatinating string and variables and uses ' to mark the start and end if the string, with your outfile it tried to add also single quotes. — nbk, Apr 14 '20 at 20:53
Thank you for your valuable response to my question. I would like to ask you if you could help me adapt the query that creates the sql procedure to be executed from a string in excel vba. I'm able to run in excel vba `sqlstr1 = "DROP procedure IF EXISTS `proc_add_userdata`;" conn.Execute sqlstr1` and also to run `sqlstr3 = "CALL proc_add_userdata(10);" conn.Execute sqlstr3`. But, unfortunately I am not able to write a string that creates a sql procedure. I have already searched, but found no answer. Thanks. — JOAO12, Apr 16 '20 at 18:35
use as text only the part between CREATE DEFINER= and END$$ (inclusive)and replace the $$ with a semicolon. and run this text complete betweemn two double quotes . It is like any other query. see https://stackoverflow.com/questions/52990394/query-mysql-table-with-excel-vba-odbc-connection — nbk, Apr 16 '20 at 20:35
Thank you very much for your help. Vba code is working perfectly. — JOAO12, Apr 16 '20 at 20:57

ysth · Answer 2 · 2020-04-14T00:45:44.357

0

Showing an example using just col_2 through col_6, like yours. The full query will be long but not impossibly so.

select max(col_6), max(col_5_on), max(col_4_on), max(col_3_on), max(col_2_on)
from (
    select
        id,
        sum(if(col>5,val,0)) col_6,
        sum(if(col>4,val,0)) col_5_on,
        sum(if(col>3,val,0)) col_4_on,
        sum(if(col>2,val,0)) col_3_on,
        sum(if(col>1,val,0)) col_2_on
    from (
        select id, 2 col, col_2 val from table_1
        union all
        select id, 3 col, col_3 val from table_1
        union all
        select id, 4 col, col_4 val from table_1
        union all
        select id, 5 col, col_5 val from table_1
        union all
        select id, 6 col, col_6 val from table_1
    ) split_data
    group by id
) summed_data;

edited Apr 14 '20 at 00:45

answered Apr 13 '20 at 22:44

ysth

96,171
6
121
214

thanks for yor reply, but I was wondering to write a code for 400 columns that is not too long. Is there another way to reach this goal? – JOAO12 Apr 13 '20 at 23:09
how long is too long? if your column names are on average 7 characters long, your original approach would be about 650000 bytes long, mentioning each column name about 200 times on average. this way only mentions each column three times and is much much shorter – ysth Apr 14 '20 at 00:42

How to add several columns and get the maximum value?

2 Answers2