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.



Thanks in advance.
-Solus

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

That didn't work Came up with Circular reference for Length

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?

MS access 2003

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]));

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.

thx for the help