08-31-13, 17:28 #1Registered User
- Join Date
- Jul 2013
Unanswered: Selecting values created in same query
Hi everyone, extremely new to SQL.
Okay, basically. I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example
SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);
Can I not select a value that has been calculated this query? What am I doing wrong here?
09-01-13, 10:48 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
You need to consider each of the sub-SELECT statements on its own. I'd be surprised if there was a SalTot column in you EmpTable because you are calculating an expression with that name in your outer SELECT statement.
Any version of Access will allow you to create three queries to do this job. One query will compute the SalTot expression. The next query computes the rollup using GROUP BY. The third query filters out the interesting rows from the first query using the second query.
Depending on the sophistication of the database engine that you're using you may be able to create a single SQL statement to extract the information that you want, but that will depend on which engine you are using.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.