Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Question 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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •