How to Get Separate Counts from Multiple Tables in a Single SQL Query?

*SELECT COUNT()**

FROM MULTIPLE TABLES

When working with database tables, it is sometimes necessary to get counts from multiple tables and display the results as separate columns. A common challenge arises when trying to retrieve counts from two different tables (tab1 and tab2).

Using UNION ALL you mentioned is a common method. However, this method produces a result set with only one column, with counts displayed one after the other.

To overcome this limitation and display the count in a separate column, a different SQL approach is required:

<code class="language-sql">SELECT  (
        SELECT COUNT(*)
        FROM   tab1
        ) AS count1,
        (
        SELECT COUNT(*)
        FROM   tab2
        ) AS count2
FROM    dual</code>

In this query, the SELECT statement contains two subqueries:

• The first subquery (enclosed in parentheses) gets COUNT(*) from tab1 and assigns it to the alias count1.
• The second subquery (also enclosed in parentheses) does the same thing for tab2, but assigns it to count2.

The FROM clause uses the dual table, which is a virtual table in the Oracle database. It always returns one row of data with only one column. This is necessary to create a result set structure (similar to a cross join) to accommodate the counts from both subqueries.

Therefore, this query produces an output with two columns: count1 and count2, containing the counts from tab1 and tab2 respectively. This approach ensures a clear and ordered presentation of COUNT(*) values ​​from two different tables.

The above is the detailed content of How to Get Separate Counts from Multiple Tables in a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!