Ad:

MySQL Discussion Thread
Marked Solved
Views: 6384

Apr 18th, 2009

Trying to get row count from multiple tables

Expand Post »

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.

Similar Threads

Schedule Repeat events in PHP
Deleting records from 2 tables based on checkbox in PHP
Filter/Update Multiple Records in PHP
Comparing Two Datasets in VB.NET
Hi guys please clear my doubt. in ASP.NET

Tekkno

Reputation Points: 12

Solved Threads: 7

Junior Poster

Offline

134 posts
since Apr 2009

Permalink

Apr 18th, 2009

Re: Trying to get row count from multiple tables

does this work?

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

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.

jakesee

Reputation Points: 21

Solved Threads: 5

Junior Poster

Offline

130 posts
since Jul 2008

Permalink

Apr 18th, 2009

Re: Trying to get row count from multiple tables

Worked great jake, thank you very much.

Tekkno

Reputation Points: 12

Solved Threads: 7

Junior Poster

Offline

134 posts
since Apr 2009

Permalink

Sep 1st, 2011

oca13th

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

Last edited by Oca13th; Sep 1st, 2011 at 5:03 am.

Oca13th

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Sep 2011

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Sql between ....querying dates

Next Thread in MySQL Forum Timeline: php and mysql

DaniWeb Message

Cancel Changes

User Name
Password