Lower Quartile Graph - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Lower Quartile Graph

Author

Topic

leahsmart
Posting Yak Master

133 Posts

Posted - 2005-02-02 : 08:31:27

I have a statement of:

SELECT AVG(Score), Measure FROM tblScore GROUP BY Measure

What I want to do is change it so that it returns the AVG of the scores that came below the avg score for each measure.

Is this possible?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-02 : 08:52:38

Does this work for you


SELECT a.Measure, AVG(a.Score)
FROM tblScore a INNER JOIN 
		(SELECT Measure, AVG(Score) AS AvgScore
			FROM tblScore 
			GROUP BY Measure) b
ON a.Measure = b.Measure
WHERE a.Score < b.AvgScore
GROUP BY a.Measure

Andy

leahsmart
Posting Yak Master

133 Posts

Posted - 2005-02-02 : 09:00:29

Thanks. I think it is working. I am checking the data now.

I did not realise you could inner join on a sub select. I am very confused over the whole thing.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 09:37:42

And here is an alternative.
( This is using a subselect, Andy used a "derived table" )

SELECT a.Measure, AVG(a.Score)
FROM tblScore a
WHERE a.Score < ( SELECT AVG(b.Score) FROM tblScore b WHERE a.Measure = b.Measure )
GROUP BY a.Measure

rockmoose

leahsmart
Posting Yak Master

133 Posts

Posted - 2005-02-02 : 10:08:01

Great! That works too.

I tried something like that before but the a.Measure = b.Measure kept giving me errors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources