Member Avatar for Tekkno

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.

  • 3 Contributors
  • 3 Replies
  • 194 Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by Oca13th
Member Avatar for jakesee

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
Member Avatar for Tekkno

Worked great jake, thank you very much.

Member Avatar for Oca13th

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

Edited by Oca13th because: n/a
Be a part of the DaniWeb community

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