Wednesday, 3 December 2014

Fix a Problem with Aliasing

We are aware of aliasing in SQL Server , but its impact is huge.

You are presented with another grouped query that fails, this time because of
an aliasing problem. As in the first exercise, you are provided with instructions on how to fix
the query.
              1. Clear the query window, type the following query, and execute it.
                   SELECT OrgID, SUM(SaleOrders) AS SaleOrders
                   FROM Sales.Orders
                   WHERE  SaleOrders > 20000
                   GROUP BY OrgID;

The query was supposed to return only shippers for whom the SaleOrders value is
greater than 20,000, but instead it returns an empty set. Try to identify the problem in
the query.

2. Remember that the WHERE filtering clause is evaluated per row—not per group. The
query filters individual orders with a freight value greater than 20,000, and there are
none. To correct the query, you need to apply the filter per each shipper group—not
per each order. You need to filter the total of all freight values per shipper. This can be
achieved by using the HAVING filter. You try to fix the problem by using the following
              SELECT OrgID,  SUM(SaleOrders) AS TotalSaleOrders
              FROM Sales.Orders
             GROUP BY OrgID
             HAVING TotalSaleOrders > 20000
But this query also fails. Try to identify why it fails and what needs to be revised to
achieve the desired result

The problem now is that the query attempts to refer in the HAVING clause to the alias
totalfreight, which is defined in the SELECT clause. The HAVING clause is evaluated
before the SELECT clause, and therefore, the column alias isn’t visible to it. To fix the
problem, you need to refer to the expression SUM(freight) in the HAVING clause, as

SELECT Orgid, SUM(SaleOrders) AS TotalSaleOrders
FROM Sales.Orders
HAVING SUM(SaleOrders) > 20000.00

[Note : Reference - ]