#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Posts
    228
    Rep Power
    0

    AVG() in small groups


    How do you use the AVG api in sql to manage grouped output? For example:
    Given a SQL Table “Employee”



    Name Salary City

    John Doe 15000 Seattle
    Jane Doe 30000 Redmond
    Tim Wayne 25000 Seattle


    Write a SQL query that would return the average salary per city in the format shown below:



    City Salary

    Seattle 20000
    Redmond 30000



    Select City, AVG(Salary) from Employee where ... I don't know the rest

    Where city == city or something like that?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    i don't think you need a WHERE clause -- use the GROUP BY clause
    Code:
    GROUP BY city
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Posts
    228
    Rep Power
    0

    do you mean somethinglike this?


    Do you mean something like this?


    select city
    ,avg(salary) as avgsalary
    from employee
    group by city
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by complete
    Do you mean something like this?
    what happened when you tested that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo