By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dataset (LocationKey INT, DateKey DATE, GrossSales MONEY)
INSERT dataset
VALUES
(1, '2022-12-01', 100),
(1, '2022-12-02', 200), -- Highest when summed
(1, '2022-12-02', 300), -- Highest when summed
(1, '2022-12-03', 400),
(2, '2022-12-01', 4000), -- Highest
(2, '2022-12-01', 3000),
(2, '2022-12-02', 2000),
(2, '2022-12-03', 1000),
(3, '2022-12-01', 444), -- Only date
(3, '2022-12-01', 555), -- Only date
(4, '2022-12-01', 2222), -- Tied for highest when summed
(4, '2022-12-01', 3333), -- Tied for highest when summed
(4, '2022-12-02', 4444),
(4, '2022-12-03', 5555) -- Tied for highest
-- Using ROW_NUMBER() which only selects one of a tie
SELECT
DateKey,
GrossSales,
LocationKey
FROM (
SELECT
DateKey,
SUM(GrossSales) AS GrossSales,
LocationKey,
ROW_NUMBER() OVER(PARTITION BY LocationKey ORDER BY SUM(GrossSales) DESC) AS RN
FROM
dataset
GROUP BY
DateKey, LocationKey
) A
WHERE RN = 1
ORDER BY
| DateKey | GrossSales | LocationKey |
|---|---|---|
| 2022-12-02 | 500.0000 | 1 |
| 2022-12-01 | 7000.0000 | 2 |
| 2022-12-01 | 999.0000 | 3 |
| 2022-12-01 | 5555.0000 | 4 |
| DateKey | GrossSales | LocationKey |
|---|---|---|
| 2022-12-02 | 500.0000 | 1 |
| 2022-12-01 | 7000.0000 | 2 |
| 2022-12-01 | 999.0000 | 3 |
| 2022-12-01 | 5555.0000 | 4 |
| 2022-12-03 | 5555.0000 | 4 |
| DateKey | GrossSales | LocationKey | RN | RK |
|---|---|---|---|---|
| 2022-12-01 | 100.0000 | 1 | 3 | 3 |
| 2022-12-02 | 500.0000 | 1 | 1 | 1 |
| 2022-12-03 | 400.0000 | 1 | 2 | 2 |
| 2022-12-01 | 7000.0000 | 2 | 1 | 1 |
| 2022-12-02 | 2000.0000 | 2 | 2 | 2 |
| 2022-12-03 | 1000.0000 | 2 | 3 | 3 |
| 2022-12-01 | 999.0000 | 3 | 1 | 1 |
| 2022-12-01 | 5555.0000 | 4 | 1 | 1 |
| 2022-12-02 | 4444.0000 | 4 | 3 | 3 |
| 2022-12-03 | 5555.0000 | 4 | 2 | 1 |