Why do we need nested Select statement?

SELECT  A.country 
       ,A.market 
       ,SUM(COALESCE(A.sales_value,0)) AS local_currency_sales
FROM 
(
    SELECT  country 
           ,market 
           ,local_currency_sales
    FROM TABLE 
) A
GROUP BY  A.country
         ,A.market

The above is the pseudo code I am referring to. I am new to SQL and would like to ask the reason that if there is a need to have a nested Select like this above? I tried to remove the nested Selected and it throws an error: country must appear in the Group By Clause. What I would like to know is that intuitively, the below should work even with Group by

SELECT  A.country 
       ,A.market 
       ,SUM(COALESCE(A.sales_value,0)) AS local_currency_sales

    FROM TABLE A
GROUP BY  A.country
         ,A.market

Solution

Your original post does not need a sub query at all, in fact the first query will not execute because it is trying to perform a SUM over a column that is not defined. The following adaptation might help explain how this works:

SELECT  A.CName
       ,A.M
       ,SUM(A.Sales) AS local_currency_sales
FROM 
(
    SELECT  country as CName
           ,market as M
           ,COALESCE(A.sales_value,0) as Sales
    FROM TABLE 
) A
GROUP BY  A.CName
         ,A.M

The use of Sub-Queries is usually to make the query easier to read or maintain. In this example you can see that the nested query evaluates the COALESCE function and has aliased the column names.

It is important to note that the outer query can only access the columns that are returned from the inner query AND that they can only be accessed by aliases that have been assigned.

In advanced scenarios you might use nested queries to manually force query optimisations, however most database engines will have very good query optimisations by default, so it is important to recognise that nested queries can easily get in the way of standard optimisations and if done poorly may negatively affect performance.

There are of course some types of expressions that cannot be used in Order By, and Group By clauses. When you come across these conditions it might be neccessary to nest the query so that you can group or sort by the results of those evaluations.

Window functions is a prime example of this requirement.

The specific queries where this becomes important are different for each RDBMS, and they each have their own workarounds or alternate implementations that may be more efficient than using a sub-query at all. The specifics are outside the scope of this post.

The simple form of your query that you have posted is all you need in this case:

SELECT  A.country 
       ,A.market 
       ,SUM(COALESCE(A.sales_value,0)) AS local_currency_sales
FROM TABLE A
GROUP BY  A.country
         ,A.market