I have around 20 tables in my database. I want to count every row of every table using COUNT and get a total. I have tried doing a separate select statement for each table then using UNION but I cannot find a way to join the results to get just one total number. Another thing I want to mention is that a lot of the tables are empty, but will later have data in them. Could someone please give me an idea of what kind of query to do? Thanks.

9 Years
Discussion Span
Last Post by Oca13th

does this work?

SELECT COUNT(x.*) AS countx, COUNT(y.*) AS county, COUNT(z.*) AS countz, (countx+county+countz) AS total FROM mytable1 AS x, mytable2 AS y, mytable3 AS x

Edit: No. but the following works

SELECT countx+county+countz AS total FROM
(SELECT COUNT( * ) AS countx FROM table1) AS x, 
(SELECT COUNT( * ) AS county FROM table2) AS y,
(SELECT COUNT( * ) AS countz FROM table3) AS z

SELECT (COUNT(x.*)+COUNT(y.*)+COUNT(z.*)) AS total FROM mytable1,mytable2, mytable3

Edited by Oca13th: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.