Using subquery's alias in a WHERE statement
Solution 1
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;
Solution 2
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;
Solution 3
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
Tommy B.
🙋🏼♂️ I'm a full-time freelance developer and open-source contributor who've been building mobile, web and software solutions for the past 15 years in multiple industries including the Medical/Health Industry, Adult Industry, Social Media, SaaS, VoIP, VPN, Web Hosting, E-Commerce & Marketing, ERPs, Automobile, Metallurgy and more. 🌟 Expertise areas: PHP, NodeJS, ExpressJS, C#, Visual Basic, MySQL, PostgreSQL, SQL Server, MongoDB, REST APIs, Heroku, DigitalOcean, AWS (Lambda, EC2, S3, RDS and more), VueJS, React, Angular, Ionic, TypeScript, Bootstrap, SCSS and more. 👨💻 Why choose me? I'm always available and extremely responsive. That's what passion is! I focus not only on delivering value, but I also improve all the projects I touch by bringing suggestions and ideas to every client I work with. I encourage you to take a look at my work history and other related profiles. (I'm active on GitHub, StackOverflow and LinkedIn) Contact me at anytime, thanks for checking out my profile! 👋
Updated on August 07, 2022Comments
-
Tommy B. over 1 year
I'm trying to use an alias created in a SELECT, but in a WHERE statement. I know it doesn't work and I just read why in another SO question.
But my question is : what other solution should I take to 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;
I know that 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'm pretty sure it isn't very performance-wise so that is the reason I'm asking for a better solution here.
Thanks!