Alias name issue in SQL

Question

I was trying to write a query for the SQL Server sample DB Northwind. The question was: "Show the most recent five orders that were purchased by a customer who has spent more than $25,000 with Northwind."

In my query the Alias name - "Amount" is not being recognized. My query is as follows:

select top(5) a.customerid, sum(b.unitprice*b.quantity) as "Amount", max(c.orderdate) as Orderdate
from customers a join orders c
on a.customerid = c.customerid
join [order details] b
on c.orderid = b.orderid
group by a.customerid
--having Amount > 25000     --throws error
having sum(b.unitprice*b.quantity) > 25000  --works, but I don't think that this is a good solution
order by Orderdate desc

Pls let me know what I am doing wrong here, as I am a newbie in writing T Sql. Also can this query and my logic be treated as production level query?

TIA,

@BhuminVadalia The TOP(5) on its own not, but together with ORDER BY OrderDate DESC, yes. The ordering puts the most recent ones first, then the top cuts the result to output just the very first 5 of them. — Alejandro
– Alejandro, Commented Aug 23, 2014 at 5:29
You can't use Order by OrderDate in your query because OrderDate column not in you group by columns. — mehdi lotfi
– mehdi lotfi, Commented Aug 23, 2014 at 5:30

Ahz · Accepted Answer · 2014-08-23 05:45:29Z

1

You must use the aggregate in the query you have. This all has to do with the order in which a SELECT statement is executed. The syntax of the SELECT statement is as follows:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

The order in which a SELECT statement is executed is as follows. Since the SELECT clause isn't executed until after the HAVING clause, you can't use the alias like you can in the ORDER BY clause.

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Reference Article: http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

answered Aug 23, 2014 at 5:45

Ahz

3611 gold badge2 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

XMarshall Over a year ago

Ahz: Thanks for the info. This probably explains as to why the Order By clause can be used directly with aliases, as already mentioned by Alejandro

Alejandro · Accepted Answer · 2014-08-23 16:42:48Z

0

This is a known limitation in SQL Server, at least, but no idea if it's a bug, intentional or even part of the standard. But the thing is, neither the WHERE or HAVING clauses accept an alias as part of their conditions, you must use only columns from the original source tables, which means that for filtering by calculated expressions, you must copy-paste the very same thing in both the SELECT and WHERE parts.

A workaround for avoiding this duplication can be to use a subquery or cte and apply the filter on the outer query, when the alias is just an "input" table:

WITH TopOrders AS (
    select a.customerid, sum(b.unitprice*b.quantity) as "Amount", max(c.orderdate) as Orderdate
    from customers a join orders c
    on a.customerid = c.customerid
    join [order details] b
    on c.orderid = b.orderid
    group by a.customerid
    --no filter here
    order by Orderdate desc
)
SELECT TOP(5) * FROM TopOrders WHERE Amount > 25000 ;

Interesting enough, the ORDER BY clause does accepts aliases directly.

edited Aug 23, 2014 at 16:42

answered Aug 23, 2014 at 5:41

Alejandro

7,9274 gold badges40 silver badges70 bronze badges

2 Comments

XMarshall Over a year ago

When I am executing both our queries, the results seem to be different. My query is returning 5 rows, while yours is returning 2 rows. As I am seeing it your internal or subquery is similar to mine. Only the filter is applied outside to the subquery. Unable to understand this difference. Also you were right about Order By clause accepting the alias directly (for e.g. OrderDate in the above query and in mine also).

Alejandro Over a year ago

Ups, yeah, my fault, I just realized. Problem is the TOP inside the subquery, it must really be in the outer one, because it would take the first 5, then remove the lower amounts, while in fact it should first remove the lower amounts, then take 5 from the remaining ones. Will fix the top issue. Yes, the inner query is a simply a copy-paste of yours, without the filter and the top (moved to the outer query), but otherwise the same.

mehdi lotfi · Accepted Answer · 2014-08-23 05:30:07Z

0

You must use Where b.unitprice*b.quantity > 25000 instead of having Amount > 25000. Having used for aggregate conditions. Your business determine your query condition. If you need to calculate sum of prices that have above value than 25000, must be use Where b.unitprice*b.quantity > 25000 and if you need to show customer that have total price above than 25000 must be use having Amount > 25000 in your query.

select top(5) a.customerid, sum(b.unitprice*b.quantity) as Amount, max(c.orderdate) as Orderdate
from customers a 
JOIN orders c ON a.customerid = c.customerid
join [order details] b ON c.orderid = b.orderid
group by a.customerid
having sum(b.unitprice*b.quantity) > 25000  --works, but I don't think that this is a good solution
Order by Amount

edited Aug 23, 2014 at 5:30

answered Aug 23, 2014 at 5:09

mehdi lotfi

11.6k18 gold badges85 silver badges128 bronze badges

1 Comment

XMarshall Over a year ago

mehdi lotfi: Thanks for the information. But your query is not returning any rows. While my earlier query returned 5 rows. I am checking again. Also, is there no way of using the Alias - "Amount" while further filtering my query. I mean, everytime I need to write - "sum(b.unitprice*b.quantity)" instead of Amount. Thnks

Roger Wolf · Accepted Answer · 2014-08-23 08:41:50Z

0

I don't have that schema at hand, so table' and column' names might go a little astray, but the principle is the same:

select top (5) ord2.*
from (
    select top (1) ord.CustomerId
    from dbo.Orders ord
        inner join dbo.[Order Details] od on od.OrderId = ord.OrderId
    group by ord.CustomerId
    having sum(od.unitPrice * od.Quantity) > $25000
    ) sq
    inner join dbo.Orders ord2 on ord2.CustomerId = sq.CustomerId
order by ord2.OrderDate desc;

answered Aug 23, 2014 at 8:41

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

Comments

Vignesh Kumar A · Accepted Answer · 2014-08-23 09:39:46Z

0

The Having Clause will works with aggregate function like SUM,MAX,AVG..

You may try like this

SELECT TOP 5 customerid,SUM(Amount)Amount , MAX(Orderdate) Orderdate
FROM
(
      SELECT A.customerid, (B.unitprice * B.quantity) As "Amount", C.orderdate As Orderdate
      FROM customers A JOIN orders C ON A.customerid = C.customerid
                       JOIN [order details] B ON C.orderid = B.orderid
) Tmp
GROUP BY customerid
HAVING SUM(Amount) > 25000
ORDER BY  Orderdate DESC

edited Aug 23, 2014 at 9:39

answered Aug 23, 2014 at 5:38

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

3 Comments

Sameer Azazi Over a year ago

I think your Having clause is wrong... it tries to SUM a Amount column from projection but there is no Amount column in projection..

XMarshall Over a year ago

@Vignesh Kumar: I am getting the following error: "Column "Tmp.Orderdate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

Vignesh Kumar A Over a year ago

@XMarshall I have updated the query. Please check it

Anup Agrawal · Accepted Answer · 2014-08-23 17:18:37Z

0

The question is little ambiguos.

Show the most recent five orders that were purchased by a customer who has spent more than $25,000 with Northwind.

Is it asking to show the 5 recent orders by all the customers who have spent more than $25,000 in all of their transactions (which can be more than 5).

The following query shows all the customers who spent $25000 in all of their transactions (not just the recent 5).

In one of the Subquery BigSpenders it gets all the Customers who spent more than $25000.
Another Subquery calculates the total amount for each order.
Then it gets rank of all the orders by OrderDate and OrderID.
Then it filters it by Top 5 orders for each customer.

--

SELECT *
FROM   (SELECT C.customerid,
               C.orderdate,
               C.orderid,
               B3.amount,
               Row_number()
                 OVER(
                   partition BY C.customerid
                   ORDER BY C.orderdate DESC, C.orderid DESC) Rank
        FROM   orders C
               JOIN
               --Get Amount Spend Per Order
               (SELECT b2.orderid,
                       Sum(b2.unitprice * b2.quantity) AS Amount
                FROM   [order details] b2
                GROUP  BY b2.orderid) B3
                 ON C.orderid = B3.orderid
               JOIN
               --Get Customers who spent more than 25000
               (SELECT c.customerid
                FROM   orders c
                       JOIN [order details] b
                         ON c.orderid = b.orderid
                GROUP  BY c.customerid
                HAVING Sum(b.unitprice * b.quantity) > 25000) BigSpenders
                 ON C.customerid = BigSpenders.customerid) X
WHERE  X.rank <= 5

edited Aug 23, 2014 at 17:18

answered Aug 23, 2014 at 6:10

Anup Agrawal

6,6891 gold badge30 silver badges33 bronze badges

2 Comments

XMarshall Over a year ago

OneZeroPundit: I am getting the following error on compiling: "Msg 8120, Level 16, State 1, Line 1 Column 'orders.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Msg 209, Level 16, State 1, Line 9 Ambiguous column name 'orderid'."

Anup Agrawal Over a year ago

@XMarshall Updated the answer. Check now. These queries are not tested as I dont have access to databases.

Collectives™ on Stack Overflow

Alias name issue in SQL

6 Answers 6

1 Comment

2 Comments

1 Comment

Comments

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

1 Comment

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related