Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Unanswered: Select rows that are above average in their DEPT

    Hi everybody,

    Could someone please help me with this situation ?

    I have a SQL Server 2000 table with four columns

    Name
    Month
    Dept
    Amount

    In this table Data are as follows

    NAME MONTH DEPT AMOUNT
    SMITH 01 SALES 10.90
    SMITH 02 SALES 12.10
    NANCY 01 PROD 10.00
    NANCY 02 PROD 10.00
    JONES 01 SALES 11.00
    JONES 02 SALES 13.50
    JONES 07 SALES 16.50
    .
    .
    .
    .

    I want to select from this table all rows that have avg(amount)
    for each Name, greater than the average of THEIR Dept (SALES for Smith and Jones, PROD for Nancy).

    The tricky part is that if there is even one Amount greater than the average of the Dept then I want all rows for this NAME

    For example in the SALES Dept the average is 12.8.
    SMITH is always below the average
    JONES has two rows above the average.
    I want all three rows for Jones

    Is there one SQL statement that I could use ? I know I can work around with temporary tables, but I am looking for a single SQL statement

    Any help appreciated,

    Thanks all and merry Christmas

    Terry

  2. #2
    Join Date
    Oct 2003
    Posts
    16

    Re: Select rows that are above average in their DEPT

    Originally posted by TerryVlisidis
    Hi everybody,

    Could someone please help me with this situation ?

    I have a SQL Server 2000 table with four columns

    Name
    Month
    Dept
    Amount

    In this table Data are as follows

    NAME MONTH DEPT AMOUNT
    SMITH 01 SALES 10.90
    SMITH 02 SALES 12.10
    NANCY 01 PROD 10.00
    NANCY 02 PROD 10.00
    JONES 01 SALES 11.00
    JONES 02 SALES 13.50
    JONES 07 SALES 16.50
    .
    .
    .
    .

    I want to select from this table all rows that have avg(amount)
    for each Name, greater than the average of THEIR Dept (SALES for Smith and Jones, PROD for Nancy).

    The tricky part is that if there is even one Amount greater than the average of the Dept then I want all rows for this NAME

    For example in the SALES Dept the average is 12.8.
    SMITH is always below the average
    JONES has two rows above the average.
    I want all three rows for Jones

    Is there one SQL statement that I could use ? I know I can work around with temporary tables, but I am looking for a single SQL statement

    Any help appreciated,

    Thanks all and merry Christmas

    Terry

    Maby that query can help you
    sory ... i dont understand everithin because my englais is not very good but...

    Select min(NAME), min(MONTH), min(DEPT),avg(AMOUNT)
    From [TableName]
    Group BY NAME, DEPT
    order by AMOUNT

    merry Christmas

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select t1.name, t1.month, t1.dept, t1.amount
    from table t1
    where (select avg(amount) from table where t1.name = name)
    > (select avg(amount) from table where dept = t1.dept);
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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