How to reference a custom field in SQL

Question

I am using mssql and am having trouble using a subquery. The real query is quite complicated, but it has the same structure as this:

select 
  customerName, 
  customerId,
  (
    select count(*) 
    from Purchases 
    where Purchases.customerId=customerData.customerId
  ) as numberTransactions
from customerData

And what I want to do is order the table by the number of transactions, but when I use

order by numberTransactions

It tells me there is no such field. Is it possible to do this? Should I be using some sort of special keyword, such as this, or self?

Answer 1

use the field number, in this case:

order by 3

Answer 2

Sometimes you have to wrestle with SQL's syntax (expected scope of clauses)

SELECT *
FROM
(
select
  customerName,
  customerId,
  (
    select count(*)
    from Purchases
    where Purchases.customerId=customerData.customerId
  ) as numberTransactions
from customerData
) as sub
order by sub.numberTransactions

Also, a solution using JOIN is correct. Look at the query plan, SQL Server should give identical plans for both solutions.

Answer 3

Do an inner join. It's much easier and more readable.

select 
customerName,
customerID,
count(*) as numberTransactions
from
    customerdata c inner join purchases p on c.customerID = p.customerID
group by customerName,customerID
order by numberTransactions

EDIT: Hey Nathan,

You realize you can inner join this whole table as a sub right?

Select T.*, T2.*
From T inner join 
(select 
customerName,
customerID,
count(*) as numberTransactions
from
    customerdata c inner join purchases p on c.customerID = p.customerID
group by customerName,customerID
) T2 on T.CustomerID = T2.CustomerID
order by T2.numberTransactions

Or if that's no good you can construct your queries using temporary tables (#T1 etc)