How to count rows from multiple tables MySQL

Question

How can I count all the rows from 2 tables where...

`approved` == '0'

?

Adam Dymitruk · Accepted Answer · 2011-06-26 23:23:52Z

4

You need to do an aggregate on a union:

select sum(subcount) as totalcount 
    from ( select count(*) as subcount from table1 where approved = 0
           union
           select count(*) as subcount from table2 where approved = 0 )

There are other ways but this shows your intent very clearly, especially naming the columns this way.

Hope this helps

answered Jun 26, 2011 at 23:23

Adam Dymitruk

131k28 gold badges149 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andriy M Over a year ago

Will return incorrect value if the two sub-counts are identical. UNION ALL instead of UNION should fix the problem.

secondman Over a year ago

Throws the following error: Error: Every derived table must have its own alias

datasage · Accepted Answer · 2011-06-26 23:19:57Z

-1

You would do this for each table:

SELECT count(*) FROM table WHERE approved = 0

You could combine it into one query but that gets more complicated. Either use a subquery or union.

answered Jun 26, 2011 at 23:19

datasage

19.7k2 gold badges51 silver badges55 bronze badges

Comments

dialogik · Accepted Answer · 2014-09-18 07:28:24Z

-1

This answer will help you:

$query = "select count(*)+(select count(*) from table1) as totalrows from table2";

edited Sep 18, 2014 at 7:28

dialogik

9,59218 gold badges79 silver badges122 bronze badges

answered Sep 18, 2014 at 7:04

user4053322

1

Collectives™ on Stack Overflow

How to count rows from multiple tables MySQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related