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.

Recommended Answers

All 3 Replies

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

Worked great jake, thank you very much.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.