How to make a query where every column is a parallel count of a subquery?

Question

I need to render a query such that every column contains the count of a respective table.

The code I have now is:

SELECT COUNT(table1.Id), 
    COUNT(table2.Id),
    COUNT(table3.Id)
FROM table1, 
     table2, 
     table3
WHERE table1.done = 'No' OR
    table2.done = 'No'  OR    
    table3.done = 'No'  OR

But I need the query to return the same result values as if every table would be counted independently, like:

SELECT COUNT(tableX.Id) FROM tableX WHERE talbeX.done = 'No'

where the 'X' stands for 1,2 or 3.

How can this be achived with SQL? Thanks beforhand for the help.

Answer 1

Just use a nested sub query, exactly as you have explained it:

SELECT 
    (SELECT COUNT(table1.Id) FROM table1 WHERE table1.done = 'No') as T1Count,
    (SELECT COUNT(table2.Id) FROM table2 WHERE table2.done = 'No') as T2Count,
    (SELECT COUNT(table3.Id) FROM table3 WHERE table3.done = 'No') as T3Count,
    (SELECT COUNT(tableN.Id) FROM tableN) as TNCount;

This will query the tables independently so you are free to use what ever additional criteria you may need without trying to correlate the results from each query

FROM in this case is not strictly necessary in the outer query as we are not returning rows from any specific table, there is no table that we could specify in the from clause. Each RDBMS has their own convention for these types of queries, MS SQL Server and Oracle are to predominant database engines used in Outsystems

• If we did specify a table in FROM then this would return 1 row for every record in that table, which is inefficient and not required. So it is important that we do not include a FROM clause.

Transact-SQL - FROM
The FROM clause is usually required on the SELECT statement. The exception is when no table columns are listed, and the only items listed are literals or variables or arithmetic expressions.

ORACLE - DUAL Table DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table.

---

Update - OP is using Oracle!

After attempting the solution, OP responded that it raised the following error:

Error in advanced query SQL2: ORA-00923: FROM keyword not found where expected

The ORA prefix of this error number indicates that the data store is actually an Oracle implementation, so we need to append the FROM DUAL to the query.

SELECT 
    (SELECT COUNT(table1.Id) FROM table1 WHERE table1.done = 'No') as T1Count,
    (SELECT COUNT(table2.Id) FROM table2 WHERE table2.done = 'No') as T2Count,
    (SELECT COUNT(table3.Id) FROM table3 WHERE table3.done = 'No') as T3Count,
    (SELECT COUNT(tableN.Id) FROM tableN) as TNCount
FROM DUAL;