forum

Home / DeveloperSection / Forums / mySQL union with calculated row

Ask Question

mySQL union with calculated row

Dag Hammarskjold259409-Jul-2013
Hi Expert!

I have a query similar to this:

select 'table_1', count(*)
from table_1
union 
select 'table_2', count(*)
from table_2
union 
select 'table_n', count(*)
from table_n
returning the total of rows for each table (n tables).

  table_1 | 100
  table_2 | 150
  table_n | 400
I want to know if there is a mysql function that can just add a new record at the end making the sum of all rows like this:

  table_1 | 100
  table_2 | 150
  table_n | 400
  total   | 650
Is there a way to do that in mySQL (version 5.5) whithout using a procedure? (for exemple using a variable inside the sql if supported)

Thanks in advance! 

mysqlmysql 
Updated on 09-Jul-2013
Dag Hammarskjold

Dag Hammarskjold

Follow

Can you answer this question?

Answer

1 Answers

AVADHESH PATEL

AVADHESH PATEL

09-Jul-2013
Hi,

Try this

select ifnull(table_name,'Total'), sum(row_count)
       from (select 'table_1' table_name, count(*) row_count
            from table_1
            union 
            select 'table_2' table_name, count(*) row_count
            from table_2
            union 
            select 'table_n' table_name, count(*) row_count
            from table_n ) temp
   group by table_name with rollup;

I hope it resolve your problem.

Liked By