SQL/MYSQL /Transpose table / Columns into rows and rows sum as column with new header-CodePudding

i have a table like this

           A    B    C
           1    4    7
           2    5    8
           3    6    9

     And i want result like this
      
       Columns     Values
       A           sum(A) = 6 
       B           sum(B) = 15
       C           sum(C) = 24

Its simple in Excel sheets but im stuck in MySql Appreciate the help Thanks

-- SeasonType,Sacks,SacksYards are columns

select SeasonType,
MAX(IF(SeasonType = '1',  Sacks, null)) AS 'Q1',
MAX (IF(SeasonType = '1',  SacksYards, null)) AS 'Q2' 
from t3 GROUP BY SeasonType

-- union all attempt column sacks,sacksyards table -- -- fantasydefencegame

select 'Sacks' as Sacks, 'SackYards' as SackYards, 0 as SortOrder
     union all
select Sum(Sacks)  total from fantasydefensegame
     union
select Sum(SackYards) from fantasydefensegame
     union
select sum(PointsAllowed) from fantasydefensegame
        group by SeasonType
        
select sum(Sacks) sacks from t3 
      union all
select sum(SackYards) sackyards from t3 group by SeasonType
        
**-- Another rough Attempt on Temp table**

Select sum(Sacks),sum(Sackyards) from t5 

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
      'max(case when Season = '2009' ''',
      Season,
      ''' then field_value end) ',
      Season
    )
  ) INTO @sql
FROM
  t5;
SET @sql = CONCAT('SELECT Sacks, ', @sql, ' 
 FROM t5 
 GROUP BY Season');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CodePudding user response：

This should fairly give you some ideas. Supposing we are using a test database named testdb and your original table is named test which has 3 columns i.e a,b,c . The three rows in the table are just like what you provided before. Next we can proceed to create a stored procedure. Note: The reason behind using a prepared statement to get the sum value for each column is due to the rules that column names have to be hardcoded , which can not be replaced with variables. e.g select sum(a) from test; can not be written as select sum(@column_name) from test;. By using a prepared statement, we can hardcode the column name dynamically.

delimiter //
drop procedure if exists table_sum//
create procedure table_sum (db_name varchar(20),tb_name varchar(20))
begin
declare col_name varchar(10);
declare fin bool default false;
declare c cursor for select column_name from information_schema.columns where table_schema=db_name and table_name=tb_name;
declare continue handler for not found set fin=true;
drop temporary table if exists result_tb;
create temporary table result_tb (`Columns` varchar(10),`Values` varchar(25));
open c;
lp:loop

    fetch c into col_name;
    if fin=true then
        leave lp;
    end if;
    set @stmt=concat('select sum(',col_name,') into @sum from test ;'); 
    prepare stmt from @stmt;
    execute stmt;
    deallocate prepare stmt;
    
    set @val=concat('sum(',col_name,') = ',@sum);
    insert result_tb values(col_name,@val);

end loop lp;

close c;
select * from result_tb;
end//

delimiter ;

Finally we call the procedure to get the desired output:

call table_sum('testdb','test');

CodePudding user response：

I would avoid prepared statements and dynamic sql unless I really need it. And I would use such powerful tool when I need to generalize on a value that increases, on a large set of columns.

In your specific case of the shared columns, you could use a simple approach that does the union of the three columns with their respective sum.

SELECT 'A'    AS `Columns`, 
       SUM(A) AS `Values`
FROM tab 

UNION

SELECT 'B'    AS `Columns`,
       SUM(B) AS `Values`
FROM tab 

UNION

SELECT 'C'    AS `Columns`,
       SUM(C) AS `Values`
FROM tab

Check the demo here.