Subquery with group function in SELECT statement

It may look strange from the first glance, but it will work actually.

Subquery will produce a single value – the SUM of all salaries. The query will execute successfully and return the FIRST_NAME and LAST_NAME column values of every row in EMPLOYEES table. Each row will have a third column called SAL that contains the sum of the SALARY values in EMPLOYEES table.

The confusion is coming from the similar case when you try to specify almost same query but without using a subquery.

It will fail because in this case, SUM(SALARY) is acting as a group function that must be aggregated using GROUP clause.

In the first case a SUBQUERY will produce a single value for every row, so no error arises. Moreover, this type of query does not have any sense, therefore you may think it should act differently and produce some meaningful results.

Leave a comment

Your email address will not be published. Required fields are marked *