CODEWITHSUNDEEP
sqlsql-serversql-server-2008subquery
Tommy B.
Tommy B.

Reputation: 3639

Using subquery's alias in a WHERE statement

I'm trying to use an alias created in a SELECT in a WHERE statement. It doesn't work and I read why in another SO question.

How do I make this work without repeating the subquery?

SELECT p.PatientID, p.PatientType, p.AccountNumber,
    p.FirstName + ' ' + p.LastName PatientFullName,
    p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
    (SELECT AVG(BGValue)
        FROM BloodGlucose
        WHERE PatientID = p.PatientID) AvgBG
FROM Patients p
WHERE AvgBG > 60;

This works:

SELECT p.PatientID, p.PatientType, p.AccountNumber,
    p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate,
    DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
    (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG
FROM Patients p
WHERE (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) > 60;

But I don't want to repeat that subquery. And I suspect it has poor performance.

Upvotes: 11

Views: 18623

Answers (6)

HLGEM
HLGEM

Reputation: 96552

Try using a derived table instead.

SELECT p.PatientID, p.PatientType, p.AccountNumber,
    p.FirstName + ' ' + p.LastName PatientFullName,
    p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
    bg.AvgBG 
FROM Patients p 
JOIN (SELECT PatientID, AVG(BGValue) AvgBG
    FROM BloodGlucose group by PatientID) BG
ON BG.PatientID = p.PatientID 
WHERE AvgBG > 60;

Derived tables work in sets and correlated subqueries work row-by-agonizing-row, that is why mine is faster.

Upvotes: 16

Wegged
Wegged

Reputation: 2413

SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, bg.AvgBG  
FROM Patients p  
outer apply (SELECT PatientID, AVG(BGValue) AvgBG   
FROM BloodGlucose where PatientID = p.PatientID  
 group by PatientID ) BG 
 WHERE AvgBG > 60;

this should also work pretty quickly

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

The aliases in the WHERE clause can only come from the FROM clause. Here is a way to rewrite your query:

SELECT p.PatientID, p.PatientType, p.AccountNumber,
       p.FirstName + ' ' + p.LastName as PatientFullName, p.CreatedDate,
       DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
       av.AvgBG
FROM Patients p join
     (SELECT PatientId, AVG(BGValue) as AvgBG
      FROM BloodGlucose
      group by PatientID
     ) av
     on p.PatientId = av.PatientId
WHERE av.AvgBG > 60;

Upvotes: 3

James
James

Reputation: 2841

Use a common table expression:

;WITH CTE (PatientID, PatientType, AccountNumber, PatientFullName, CreatedDate, TotalTime, AvgBG) AS (
    SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate,
    DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime,
    (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG
    FROM Patients p
)

SELECT PatientID, PatientType, AccountNumber, PatientFullName, CreatedDate, TotalTime, AvgBG
FROM CTE
WHERE AvgBG > 60

Upvotes: 1

Fyre
Fyre

Reputation: 1180

You can try to use WITH clause in standard SQL.

WITH Subquery AS
(
    Your Code
)

And then your code

SELECT ....
Subquery As sq
From Patients p
WHERE(subquery)>60

Upvotes: 1

jay c.
jay c.

Reputation: 1561

SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName p.LastName PatientFullName, p.CreatedDate,
DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, a.AvgBG
FROM Patients p,
(SELECT PatientID, AVG(BGValue) AvgBG
  FROM BloodGlucose
group by PatientID
having AVG(BGValue) > 60) a
where p.PatientID = a.PatientID

Upvotes: 0

Related Questions