MySQL

Trying to get row count from multiple tables

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Apr 2009

Posts: 64

Reputation: Tekkno is an unknown quantity at this point

Solved Threads: 1

Tekkno

Offline

Junior Poster in Training

Trying to get row count from multiple tables

Apr 18th, 2009

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.

•

Join Date: Jul 2008

Posts: 95

Reputation: jakesee is an unknown quantity at this point

Solved Threads: 4

jakesee

Offline

Junior Poster in Training

Re: Trying to get row count from multiple tables

Apr 18th, 2009

does this work?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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 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

Last edited by peter_budo; Apr 19th, 2009 at 5:05 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.