Geek Answers Handbook

Display sum in last row of table using mysql

I have the following table elements, as I run a query that plays the table but has an extra line at the bottom showing the sum of col2

Col1 | col2 --------------- Water | 22 water | 3 water | 5 Air | 10 Earth | 3 Air | 5
+7
source share
3 answers

I don’t know why you want this, but try:

 SELECT Col1, Col2 FROM tableName UNION SELECT 'SUM' as Col1, SUM(Col2) Col2 FROM tableName
+10
source 
 insert into table_name(col2) (select sum(col2) from table_name) as a

tell me if this works or not.

+4
source 
 select IFNULL(col1,'SUM') col1,sumcol2 col2 from (select col1,col2,sum(col2) sumcol2 from summation_trick group by col1,col2 with rollup) B where (col1 is null and col2 is null) or (col1 is not null and col2 is not null);

Here are your uploaded data

 mysql> use junk Database changed mysql> drop table if exists summation_trick; Query OK, 0 rows affected (0.04 sec) mysql> create table summation_trick -> ( -> col1 varchar(20), -> col2 int -> ); Query OK, 0 rows affected (0.09 sec) mysql> insert into summation_trick values -> ('Water',22 ),('water', 3 ), -> ('water', 5 ),('Air' ,10 ), -> ('Earth', 3 ),('Air' , 5 ); Query OK, 6 rows affected (0.05 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql>

and now the executed request is executed

 mysql> select IFNULL(col1,'SUM') col1,sumcol2 -> from (select col1,col2,sum(col2) sumcol2 -> from summation_trick -> group by col1,col2 -> with rollup) B -> where (col1 is null and col2 is null) -> or (col1 is not null and col2 is not null); +-------+---------+ | col1 | sumcol2 | +-------+---------+ | Air | 5 | | Air | 10 | | Earth | 3 | | water | 3 | | water | 5 | | Water | 22 | | SUM | 48 | +-------+---------+ 7 rows in set (0.00 sec) mysql>
+3
source

More articles:


All Articles