#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Vancouver, WA
    Posts
    4
    Rep Power
    0

    Compare Aggregates


    I have two SQL queries which work one provides the avg number of sales by department and one which shows the total sales by dept. I am trying to combine the queries to show the deptartments that sold less than the average:

    SELECT deptname, SUM(saleqty)
    FROM qsale
    WHERE saleqty < (SELECT SUM(saleqty) / COUNT (DISTINCT deptname)
    FROM qsale)
    GROUP BY deptname

    I'm trying to understand how to do this comparison. The saleqty in my WHERE clause does not reflect the SUM(saleqty). How should this be formatted?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    untested:
    Code:
    select deptname, sum(saleqty)
      from qsale
    group 
        by deptname
    having sum(saleqty)
         < ( select sum(saleqty) 
                  / count(distinct deptname)
               from qsale )
    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Vancouver, WA
    Posts
    4
    Rep Power
    0
    Thanks Rudy, it worked fine. I did not think to place the sub select in the having clause...

IMN logo majestic logo threadwatch logo seochat tools logo