How to count rows from two tables in one query?

Question

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...

Please edit your question and include sample data and desired results. As written, it is unclear what you are asking. — Gordon Linoff, Mar 31 '15 at 13:55

shA.t · Accepted Answer · 2017-09-05T15:52:01.907

3

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]

edited Sep 05 '17 at 15:52

answered Mar 31 '15 at 14:16

shA.t

16,580
5
54
111

What must be instead `[FROM DUAL]`? – Sahe Mar 31 '15 at 14:22
@Sahe, that two sub-queries come between your `select` and `from`, well I have not your complete query `FROM DUAL` is for your test. – shA.t Mar 31 '15 at 14:27

score 2 · Answer 2 · edited Apr 07 '16 at 18:13

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.

How to count rows from two tables in one query?

2 Answers2

Linked