CODEWITHSUNDEEP
mysqlsql
Sahe
Sahe

Reputation: 163

How to count rows from two tables in one query?

I know that one can use UNION on a select statement if count of columns in two tables is equal. Also, another option is using sub-query in the select clause. What else can I use?

Example:

tabel1  table2
id  1       1 
    2       2
    3       3

I need to get the number of total rows from both tables in one query:

...COUNT(table1.id) as tbc1, COUNT(table2.id) as tbc2...

Upvotes: 3

Views: 6069

Answers (2)

shA.t
shA.t

Reputation: 16958

Use sub-queries and if it needs add FROM DUAL:

SELECT 
    (SELECT COUNT(*) FROM TABLE1) As Table1Count, 
    (SELECT COUNT(*) FROM TABLE2) As Table2Count
[FROM DUAL]

Upvotes: 3

AdamMc331
AdamMc331

Reputation: 16691

If you do a cross join between the two tables, you'll get a lot more rows than you really need here. You'll get a cartesian product of the tables, so the number of rows would be the number in table one multiplied by the number in table two.

However, a cross join can still be used if you preform aggregation on the id values of the two tables, using COUNT(distinct [column]).

Try this:

SELECT COUNT(distinct t1.id) + COUNT(distinct t2.id) AS totalRows
FROM firstTable t1, secondTable t2;

This query counts the distinct id values that come from the first table (which is essentially the number of rows) and adds it with the number of rows from the second table as well. It worked in SQL Fiddle.

Upvotes: 2

Related Questions