Home > DeveloperSection > Forums > mySQL union with calculated row
Dag Hammarskjold
Dag Hammarskjold

Total Post:24

Points:168
Posted on    July-09-2013 3:25 AM

 MYSQL MYSQL 
Ratings:


 1 Reply(s)
 1006  View(s)
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! 



AVADHESH PATEL

Total Post:604

Points:4228
Posted on    July-09-2013 4:43 AM

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.

Don't want to miss updates? Please click the below button!

Follow MindStick