Rhino wrote:
----- Original Message ----- From: "Martin Rytz" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, November 24, 2004 8:47 AM
Subject: Multiple table count in one sql-query
Hello People
I should have the count from 3 tables in one query; something like this:
select count(*) from table1 union select count(*) from table2 union select count(*) from table3 union;
The Problem ist, that the query gives 3 results back (the count from table1, table2 and table3) BUT I should have only one result, the added value from all 3 tables...
How can I do this within MySQL? Are there variables I could use within the query? Other possibilities?
Thank you for your help!
One approach would be to create a temporary table consisting of one integer column, then write the results of your query to that table. Here is a script that demonstrates this technique:
#Create temporary table containing a union of the row counts from each of
create temporary table if not exists temp01 select count(*) as total_rows from Employee UNION select count(*) as total_rows from emp UNION select count(*) as total_rows from dept;
#Display the contents of the temporary table. select * from temp01;
#Sum the row counts from the different tables. select sum(total_rows) from temp01;
#Drop the temporary table. drop table if exists temp01;
This script works perfectly on my 4.0.15 system.
Rhino
You could accomplish the same logic in one go with user variables:
SELECT 'table1' AS 'table', @c1:= count(*) AS 'rows' FROM table1 UNION SELECT 'table2', @c2:= count(*) FROM table2 UNION SELECT 'table3', @c3:= count(*) FROM table3 UNION SELECT 'Total', @c1 + @c2 + @c3;
The answer is in the last row. If you don't care about the individual counts, or don't want to parse results, you can do it in two queries by separating the last SELECT from the UNIONs. In that case, you might as well simplify a little:
SELECT @rows:= count(*) FROM table1 UNION SELECT @rows:= @rows + count(*) FROM table2 UNION SELECT @rows:= @rows + count(*) FROM table3
SELECT @rows;
@rows is a running total, so the answer is in the last row of the UNION output, but you can retrieve it directly with a separate SELECT.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
