Question

What single query can get data from 4 tables , using SUM or COUNT

Answer 1

Sum() returns the total sum of a numeric column. count() returns the number of rows that matches a specific condition from the table. With out giving condition also we can give count(), so that it returns the total count of rows in the table.

Here we use sum of count from the 4 tables, so that sum() and count() will be there in the query.

To get sum of values from a table, sum() is used. Sum() can store a variable or temporary column (alias), same with count() also. Union all command combines the result set of 2 or more select statements. Union all will take duplicate values also.

So the query is:

SELECT SUM(ALIAS) [AS COLUMNNAME] FROM ((SELECT COUNT(*) AS ALIAS FROM TABLE1) UNION ALL(SELECT COUNT(*) AS ALIAS FROM TABLE2) UNION ALL(SELECT COUNT(*) AS ALIAS FROM TABLE3) UNION ALL(SELECT COUNT(*) AS ALIAS FROM TABLE4))ALIAS;

EXAMPLE:

SELECT SUM(ALLCOUNT) AS TOTALCOUNT FROM ((SELECT COUNT(*) AS ALLCOUNT FROM EMPLOYEES) UNION ALL(SELECT COUNT(*) AS ALLCOUNT FROM IRAS) UNION ALL(SELECT COUNT(*) AS ALLCOUNT FROM ADDRESSCITY) UNION ALL(SELECT COUNT(*) AS ALLCOUNT FROM COMPANT))T;