CODEWITHSUNDEEP
sql-server-2008joinsubquery
jpo
jpo

Reputation: 4059

SQL Server Referencing Nested Subqueries

In a sql server database, I am trying to return thegender with the most about of money mad ethrough bonuses. I therefore have a sub query to calculate the sum of bonuses made by each gender, another subquery to return the max and finally, my main query returns the gender with this max. However my code is not working.

SELECT E.Gender, maxx.mm

   FROM HumanResources.Employee as E, 
   (SELECT MAX(Bonus) as mm 
    FROM (SELECT E.Gender, SUM(SP.Bonus) AS Bonus 
          FROM HumanResources.Employee AS E, Sales.SalesPerson AS SP
          GROUP BY E.Gender) 
     AS gen) AS  maxx

where E.Gender = gen.Gender;

The last line :where E.Gender = gen.Gender doesn't seem to work as gen.Gender can't be bound

Any help please

Upvotes: 0

Views: 368

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

I am not a big fan of the subquery(subquery(subquery( model, as it is often wasteful and usually difficult to parse / understand / re-write. Here is how I initially wanted to re-write it when I saw it:

SELECT TOP (1) E.Gender, mm = SUM(SP.Bonus)
  FROM Sales.SalesPerson AS sp 
  INNER JOIN HumanResources.Employee AS E
  ON E.EmployeeID = sp.EmployeeID -- guess on relationship
GROUP BY E.Gender
ORDER BY mm DESC;

Another way (potentially more efficient depending on the plan, since you will eliminate all but one employee from the join - something that may happen above, but also might not):

;WITH sp AS
(
  SELECT TOP 1 EmployeeID, mm = SUM(Bonus)
    FROM Sales.SalesPerson
    GROUP BY EmployeeID
    ORDER BY mm DESC
)
SELECT E.Gender, topsp.mm
  FROM sp
  INNER JOIN HumanResources.Employee AS E
  ON E.EmployeeID = sp.EmployeeID; -- again guessing on relationship

Upvotes: 1

JNK
JNK

Reputation: 65187

Put your filter inside the subquery. You can refer to outer queries from within, so:

 AS gen) AS  maxx

becomes

 AS gen WHERE  E.Gender = gen.Gender) AS  maxx

Upvotes: 1

Related Questions