Averaging values in cross-grouping

[NHW]

Hi. I'm trying to average the data of a column according to the grouping of 2 other columns.. e.g. the raw data is

SNum Year Model Price
---- ---- ----- -----
1 2003 A 20
1 2003 B 10
2 2003 A 15
2 2003 B 10
2 2003 C 12
3 2003 B 20
1 2004 A 23
1 2004 D 14
2 2004 B 14
2 2004 C 20
.... etc etc

And I'd like to create an output of average Price according to each SNum under different combinations of Year and Model:

SNum Year Model AvgPrice
---- ---- ----- --------
1 2003 A <avg of A in 2003 of SNum 1>
1 2003 B <avg of B in 2003 of SNum 1>
2 2003 A <avg of A in 2003 of SNum 2>
2 2003 B <avg of B in 2003 of SNum 2>
2 2003 C <avg of C in 2003 of SNum 2>
3 2003 B <avg of B in 2003 of SNum 3>
1 2004 A <avg of A in 2004 of SNum 1>
1 2004 D <avg of D in 2004 of SNum 1>
.... etc etc

The problem is that I won't know beforehand what the value of Year and Model is (and they may not be in any specific sequence/pattern. For example, for Model, it could be like A, B, C, D, F, J without E, G, H, I) so I can't seem to use a WHILE loop to do it.

Thanks in advance!

 

[bborissov]

SELECT Snum, Year, Model, AVG(Price) AS AvgPrice;
       FROM MyTable
GROUP BY Snum, Year, Model
ORDER BY Year, Model, SNum

Borislav Borissov

 

[NHW]

Thanks.. what if I want to assign a ranking to the results according to Year and Model? Eg. (I just made up the AvgPrice and sorted the table for convenience) Thanks!

SNum Year Model AvgPrice Rank
---- ---- ----- -------- ----
1 2003 A 20.4 1
2 2003 A 10.5 2
3 2003 B 26.6 1
2 2003 B 17.9 2
1 2003 B 11.3 3
2 2003 C 32 1
1 2004 A 22.3 1
1 2004 D 35.4 1