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;
Advertisement
Answer
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');