The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Nested (?) SQL Group By

I'm new to creating SQL queries and was wondering if this is at all possible. "Nested" is probably not even be the right term. I know that the standard Group By can be used to produce the 2 "GroupBy Avg 1" averages, but can't seem to find any info on a SQL statement that would return an average of the averages - i.e. the number 15 shown in the last row of the following illustration. Any help on this would really be appreciated.

Table = Scores

            Class        Student    TestID    Score
            A        Jim        1        10
            A        Jim        2        12
            A        Jim        3        14
GroupBy Avg 1    A        Jim                12    
            A        Pat        1        16
            A        Pat        3        20
GroupBy Avg 1    A        Pat                18    
GroupBy Avg 2    A                        15
Robert Lerner Send private email
Monday, September 18, 2006
 
 
select subQ.class
, avg(subQ.score)
from (
select class
, student
, avg(score) as 'score'
from scores
group by class
, student
) as subQ
group by subQ.class

Something like that?

You're not trying to use SQL to do report layouts are you?
D in PHX Send private email
Monday, September 18, 2006
 
 
Thank you - it meanss that it IS possible to do what I need.

No, I'm using MS Web Developer Express and trying to use a custom SQL query to populate a grid on a form. Thanks again.
Robert Lerner Send private email
Monday, September 18, 2006
 
 
I think that the average of a set of averages is usually called the "Grand Average" (kind of like the grand total, I guess).  Might be helpful to google that and see what turns up.
Vish Send private email
Monday, September 18, 2006
 
 
No prob Rob.

Lemme know if you need more help. =)
D in PHX Send private email
Monday, September 18, 2006
 
 
Vish - thanks for the tip. Google led me to Rollup which I had never heard of before, and it looks very interesting.

D - thanks again, and I will!
Robert Lerner Send private email
Monday, September 18, 2006
 
 
You ought to be able to simpligfythat a little ...

select subQ.class, avg(subQ.score)
from
(
select class, avg(score) as 'score'
from  scores
group by class,student
) as subQ
group by subQ.class

ie. you don't student in the select clause of the in-line view.
David Aldridge Send private email
Monday, September 18, 2006
 
 
Thank you very much David.
Robert Lerner Send private email
Tuesday, September 19, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz