Help to compare all permutations of several columns in one sql server query

Question

Hi

I have a db related to horse racing performance and want to compare a horse's record under a number of different situations. For example I would like to see how it performs in the class it is due to run in, the course it is due to run at, with the person who will be riding the horse and over the distance it will run at. If we take these 4 is it possible to pull data related to all 15 combinations (4 x individual columns, 6 x pairs of columns, 4 x 3 columns and 1 of all 4 columns) of these columns in one query?

I'll explain a bit about the setup as hopefully this will help. An example query just to pull the record of the horse at the course would be something like this:

SELECT Horse_ID, Course_ID, SUM(Won) AS Wins, COUNT(*) AS Runs
FROM RaceDetails AS R
INNER JOIN HorsePerfInRace AS H ON R.RaceID = H.RaceID

The RaceDetails columns table contains the information about the race itself such as distance, course and class and then the HorsePerfInRace details how each horse performed in that race (e.g finishing position).

I am running SQL Server 2005 and am prepared to look at any approach to accomplish this. Thanks in advance for any help anyone is kind enough to offer.

Answer 1

I think you may be interested in GROUP BY with ROLLUP or CUBE. Here are a couple of queries you can try. First is with rollup.

SELECT Horse_ID, Course_ID, Class_ID, Rider_ID, Distance_ID, SUM(Won) AS Wins, COUNT(*) AS Runs
FROM RaceDetails AS R
INNER JOIN HorsePerfInRace AS H ON R.RaceID = H.RaceID
GROUP BY ROLLUP (Horse_ID, Course_ID, Class_ID, Rider_ID, Distance_ID)

And one with cube.

SELECT Horse_ID, Course_ID, Class_ID, Rider_ID, Distance_ID, SUM(Won) AS Wins, COUNT(*) AS Runs
FROM RaceDetails AS R
INNER JOIN HorsePerfInRace AS H ON R.RaceID = H.RaceID
GROUP BY CUBE (Horse_ID, Course_ID, Class_ID, Rider_ID, Distance_ID)

The syntax is similar, but the results are slightly different. You can learn more about rollup and cube here.