Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Question Unanswered: Need help with a simple set function query

    I am trying to find all materials with a length greater than the average length of all the materials put together to make the category.

    Can anybody help me?
    This is what I tried so far but it says Length has to be in the group by. If I do that I don't think it will return what I want.


    Code:
    Select Name, Length
    from materials
    group by category
    having Length > (Select avg(Length) from materials)

    Thanks in advance.
    -Solus

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    SELECT
    m1.Name,
    m1.Length
    FROM
    materials m1
    INNER JOIN (
    SELECT
    AVG(Length) AS Length,
    Category FROM materials) m2 ON m1.Category = m2.Category
    AND m1.Length > m2.Length
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Apr 2004
    Posts
    7
    That didn't work Came up with Circular reference for Length

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    If you are using SQL Server, this works:

    -- CREATE TABLE materials(
    -- Name VARCHAR(55),
    -- Category VARCHAR(55),
    -- Length INT)
    --
    -- INSERT materials(Name,Category,Length)
    -- SELECT '2x4','lumber',10
    -- UNION ALL
    -- SELECT '2x4','lumber',20
    -- UNION ALL
    -- SELECT '2x4','lumber',30
    -- UNION ALL
    -- SELECT '4x8','lumber',10
    -- UNION ALL
    -- SELECT '4x8','lumber',20
    -- UNION ALL
    -- SELECT '4x8','lumber',30
    --


    SELECT
    m1.Name,
    m1.Length
    FROM
    materials m1
    INNER JOIN (
    SELECT
    AVG(Length) AS Length,
    Category
    FROM materials
    GROUP BY Category) m2 ON m1.Category = m2.Category
    AND m1.Length > m2.Length

    If you are not using SQL Server, what database are you using?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Apr 2004
    Posts
    7
    MS access 2003

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    This is why I hate Access. Thanks for reminding me. lol

    SELECT materials.Name, materials.Category, materials.Length
    FROM materials INNER JOIN materials AS materials_1 ON materials.Category = materials_1.Category
    GROUP BY materials.Name, materials.Category, materials.Length
    HAVING (((Avg(materials_1.Length))<[materials].[Length]));
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  7. #7
    Join Date
    Apr 2004
    Posts
    7
    isn't inner join a noise word (Meaning it does nothing) sorta like "order by X asc". I'm just new to SQL and wonder why people include noise words. The only reason I could think of is that maybe some DBMS's don't work normally or as you would expect without them.

  8. #8
    Join Date
    Apr 2004
    Posts
    7
    thx for the help

Posting Permissions

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