Trying to modify SQL query to include min AVG and MAX in results

If you follow the advice given in solution 1 you will get an error returned

Quote:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

You would need to do this instead

Select emp.EmpID,
emp.EmpName,
emp.PayRate,
jt.Title,
(Select AVG(payrate) FROM yEmp) AveragePayRate,
(Select MIN(payrate) FROM yEmp) MinPayRate,
(Select MAX(payrate) FROM yEmp) MaxPayRate
FROM yEmp emp
INNER JOIN yJobTitle jt ON emp.jobtitleid = jt.jobtitleid
WHERE payrate <= (Select AVG(payrate)
FROM yemp)
ORDER BY emp.empid;

That is a truly awful query - you have two identical sub-queries

Select AVG(payrate) FROM yEmp

and a further two sub-queries on the same table
Straight away that is screaming to me to be refactored, and there are so many ways you could do it.

Example 1: Have a single sub-query that calculates all the values and do a cross-join on that

Select emp.EmpID,
		emp.EmpName,
		emp.PayRate,
		jt.Title,
	    calcs.avgrate,
		calcs.minrate,
		calcs.maxrate
FROM yEmp emp
INNER JOIN yJobTitle jt ON emp.jobtitleid = jt.jobtitleid
cross join (Select AVG(payrate) avgrate, MIN(payrate) minrate, MAX(payrate) maxrate from yEmp) as calcs
WHERE payrate <= calcs.avgrate 
ORDER BY emp.empid;

Example 2: Declare some variables, calculate them first then reference them in your query

declare @avrate decimal(15,2);
declare @minrate decimal(15,2);
declare @maxrate decimal(15,2);
Select @avrate = AVG(payrate), @minrate = MIN(payrate), @maxrate = MAX(payrate) from yEmp;

Select emp.EmpID,
		emp.EmpName,
		emp.PayRate,
		jt.Title,
	    @avrate as avgrate,
		@minrate as minrate,
		@maxrate as maxrate
FROM yEmp emp
INNER JOIN yJobTitle jt ON emp.jobtitleid = jt.jobtitleid
WHERE payrate <= @avrate 
ORDER BY emp.empid;

Example 3: Use a common table expression or temporary table to do the calculations and cross join to that

;with cte as
(
	Select AVG(payrate) avgrate, MIN(payrate) minrate, MAX(payrate) maxrate from yEmp
)
Select emp.EmpID,
		emp.EmpName,
		emp.PayRate,
		jt.Title,
	    cte.avgrate,
		cte.minrate,
		cte.maxrate
FROM yEmp emp
INNER JOIN yJobTitle jt ON emp.jobtitleid = jt.jobtitleid
cross join  cte
WHERE payrate <= cte.avgrate 
ORDER BY emp.empid

All the examples get all of the calculated values you need in a single hit on the table, rather than hitting the table once per each individual value