CODEWITHSUNDEEP
mysql
Thomas Banderas
Thomas Banderas

Reputation: 1739

Sum section of latest columns in mysql

I have table which look like:

ID | a | b | sth_CH | sth_DD | sth_FF | ...
1 | xx | yy | 1 | 5 | 3 | ...
2 | xy | yx | 3 | 1 | 6 | ...

There you could see that I can have other columns(more and more) called sth_XX ...

Question:

How to sum values from fourth column(sth_CH) to last column ? We can assume that I dont know names of column, but I know that It starts from "sth_" (but I think that is not necessary).

I want something like SUM(4,table.columns.count) and this SUM all values from fourth column to the last one.

For SUM(4,6) I want to have return:

9
10

Upvotes: 0

Views: 60

Answers (3)

Abdullah Nehir
Abdullah Nehir

Reputation: 1047

I also agree that you should normalize your data model. But if you are not able to do, this script may do the job:

set @a := ' ';

select @a := concat(@a, COLUMN_NAME, ' + ')
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA ='your schema'
    and TABLE_NAME='your table'
    and column_name not in ('ID', 'a', 'b');

set @a := LEFT(@a, length(@a) -2);
set @sql := concat('select ', @a, ' from <your table> where ...');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

Edit: I understood later that aggregation is not wanted, you need to add column values. Removed the aggregate function "sum" from the code. P.S. please mark as answer if it solved your problem.

Upvotes: 1

seahawk
seahawk

Reputation: 1912

Below code will give dynamically column names and form dynamic sql script that can be prepared and executed.

Below sql script will get dynamically get column name skipping first three and forms sql script for field list:

select group_concat(concat("sum(",column_name,") as sum_",column_name)) into @field_list from
(select @marker:=if(@marker<4,@marker+1,@marker) as marker,column_name 
from information_schema.columns,(select @marker:=0)t
where table_name='t1') temp where marker>=4 group by marker;

Generate sql script:

set @query= concat("Select ",@field_list," from t1");

Execute:

PREPARE stmt FROM @query;
EXECUTE stmt;

Assumption:- table name is t1

Upvotes: 0

Hatem Jaber
Hatem Jaber

Reputation: 2402

Just some pointers, I think that as suggested in the comments, you need to consider a redesign. I will answer based on the limited knowledge of your project and information that you provided.

You should create a table that will hold these "special codes" that you mentioned as the additional columns. Than you would have a many-to-many table that will link the special codes to your table, here is what I'm thinking:

The original table minus the array of columns that you added in your example:

ID | a  | b
1  | xx | yy
2  | xy | yx

The new special codes table:

ID | special_code
1  | sth_CH
2  | sth_DD
3  | sth_FF
...

The linking table:

original_table_id | special_code_id
        1         |        1
        1         |        2
        1         |        3
        2         |        1
        2         |        2
        2         |        3

This will allow you to have an endless number of special codes and make it easy for you to get the sums for certain ID's.

Just as a side note, it's always better to provide more information if you can without exposing your secrets. It will allow others to talk about your code etc... and directly relate it to your examples.

Upvotes: 0

Related Questions