MYSQL UNION WITH CALCULATED ROW

Dag Hammarskjold

Total Post:24

Points:168
Posted by  Dag Hammarskjold
MYSQL 
 1324  View(s)
Ratings:
Rate this:
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! 

  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: mySQL union with calculated row

    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.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!