Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2011
    Posts
    1

    Unanswered: Do not understand why do I use subquery here

    SELECT staffNo, fName, lName, position, salary - (SELECT AVG(salary) FROM Staff) AS salDiff
    FROM Staff
    WHERE salary > (SELECT AVG(salary) FROM Staff);

    I understand that WHERE does not understand aggregate functions so must use like this but I don't understand why I have to USE (SELECT AVG(salary) FROM Staff) rather than just going AVG(salary) AS salDiff because it is already from staff anyway)

    Please... let me know this thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you try using just AVG(salary) there?

    please, try it and see what happens

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Do not understand why do I use subquery here

    What about something like the following?...

    Code:
    SELECT staffNo ,fName ,lName ,position ,(salary - sav.Average) as salDiff
    FROM (SELECT AVG(salary) as Average FROM Staff) sav
    CROSS JOIN Staff stf
    WHERE stf.salary > sav.Average

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i like it, homerow... the use of CROSS JOIN especially

    the subquery is still there, though, it's just morphed into the FROM clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I think this points out a fundamental development approach for which I'd like the opinions of some of the more experienced guys out there . . .

    Looking at this simple query, in my early days I would probably have written it like this:

    Code:
    select  staffNo
            ,fName
            ,lName
            ,position
            ,salary-avgSalary salDiff
    from    Staff
    cross
    join    (
            select  avg(salary) avgSalary
            from    Staff
            ) i
    where   salary>avgSalary
    I no longer do so.

    Here's something that I would currently do:

    Code:
    declare @avgSalary decimal(9,2)=
    	(
            select  avg(salary)
            from    Staff
            )
    
    select  staffNo
            ,fName
            ,lName
            ,position
            ,salary-@avgSalary salDiff
    from    Staff
    where   salary>@avgSalary
    Excuse me for how I explain this, as my knowledge of transactions and locking is very weak, but from my actual experience I have noticed that the first methodology holds a transaction during the entire double scan of the Staff table while the second methodology breaks-up that transaction into two different transactions, allowing others to access the Staff table in-between the two scans. This may not seem like much, with as trivial a table as this, but I have found that when you are dealing with complex queries against large tables, it is best to break-up the query into smaller queries that can be executed individually, sequentially, and more quickly.

    This is from a guy who has never learned to use SQL Profiler, but it seems to me logical that the second methodology will lead to a generally improved system performance. At least I, anecdotally, have found that to be the case.

    Am I dreaming, or am I thinking in the correct direction?
    Last edited by PracticalProgram; 11-19-11 at 18:32.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

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