CODEWITHSUNDEEP
mysqlsqlsum
рüффп
рüффп

Reputation: 5448

mySQL union with calculated row

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)

Upvotes: 1

Views: 193

Answers (4)

Javide
Javide

Reputation: 2637

If you are only after the the number of rows, you should use the system table. This will have the advantage that if you are preparing the query you don't have to hard code the table names as these can be passed as parameters:

select ifnull(table_name,'Total') as table_name, sum(table_rows) as table_rows
from (
SELECT
    TABLE_NAME, 
    TABLE_ROWS 
FROM 
    INFORMATION_SCHEMA.TABLES 
WHERE
    TABLE_NAME IN ('tOne', 'tTwo', 'tThree') 
) temp
group by table_name with rollup;

Upvotes: 1

chetan
chetan

Reputation: 2886

     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;

Upvotes: 2

dijkstra
dijkstra

Reputation: 1078

select 'table_1', count(*)
from table_1
union 
select 'table_2', count(*)
from table_2
union 
select 'table_n', count(*)
from table_n
union 
select 'total', sum(a.count) 
from (
   select 'table_1', count(*)
   from table_1
   union 
   select 'table_2', count(*)
   from table_2
   union 
   select 'table_n', count(*)
   from table_n
) a

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Maybe use WITH ROLLUP:-

SELECT TableName, TableCount
FROM
(
    SELECT 'table_1' AS TableName, COUNT(*) AS TableCount
    FROM table_1
    union 
    SELECT 'table_2' AS TableName, COUNT(*) AS TableCount
    FROM table_2
    SELECT 
    select 'table_n' AS TableName, COUNT(*) AS TableCount
    FROM table_n
) Sub1
GROUP BY TableName, TableCount WITH ROLLUP

Upvotes: 1

Related Questions