I want to both group some rows together but return 1 column of the individual rows from the group onto the same line as well, probably easiest to show with an example….
Original Data
x
rDate | track | horse | odds
-----------------------------
01/06/2021 | Ascot | Jim | 3
01/06/2021 | Ascot | Dave | 3
01/06/2021 | Ascot | Rex | 15
01/06/2021 | Epsom | Lee | 2
01/06/2021 | Epsom | Ben | 3
01/06/2021 | Epsom | Gary | 4
Grouped by rDate, track, AVG(odds)
Select rDate, track, AVG(odds)
FROM Results
GROUP BY rDate, track
rDate | track | AvgOdds
-----------------------------
01/06/2021 | Ascot | 7
01/06/2021 | Epsom | 3
Desired output, grouped with average odds but with individual odds added to grouped row as well:
rDate | track | AvgOdds | odds1 | odds2 | odds3
--------------------------------------------------
01/06/2021 | Ascot | 7 | 3 | 3 | 15
01/06/2021 | Epsom | 3 | 2 | 3 | 4
The number of rows in each group varies from 4 to 8, but a solution using a fixed number of rows would be acceptable, I can work around it.
Advertisement
Answer
I would suggest conditional aggregation phrased like this:
SELECT rDate, track, AVG(odds) as avgodds,
MAX(CASE WHEN seqnum = 1 THEN odds END) as odds1,
MAX(CASE WHEN seqnum = 2 THEN odds END) as odds2,
MAX(CASE WHEN seqnum = 3 THEN odds END) as odds3
FROM (SELECT r.*,
ROW_NUMBER() OVER (PARTITION BY rDate, Track ORDER BY odds) as seqnum
FROM Results r
) r
GROUP BY rDate, track;
Note: If you want to extend this to 8 columns for odds, just follow the pattern in the SELECT
.