I have some data like this:
| TYPE | A | B | C |
|---|---|---|---|
| aaa | 5 | 6 | 2022-05-01 |
| aaa | 8 | 7 | 2022-05-08 |
| aaa | 9 | 8 | 2022-05-16 |
| bbb | 7 | 4 | 2022-05-09 |
| bbb | 6 | 8 | 2022-05-14 |
| bbb | 3 | 3 | 2022-05-25 |
I need to get an output like this:
| TYPE | A | D | C |
|---|---|---|---|
| aaa | 22 | 8 | 2022-05-16 |
| bbb | 16 | 3 | 2022-05-25 |
My current code :
SELECT type, SUM(A) AS A, SUM(D) AS D, MAX(C) AS C
FROM
(SELECT
type, A, B, C,
CASE
WHEN C = MAX(C) OVER(PARTITION BY type)
THEN B
ELSE 0
END AS D,
FROM
data) AS bbb
GROUP BY
type
Is there any better method without using a subquery?
I’m using SQL Server.
>Solution :
Since you are asking for a query without a subquery, you can try this one:
SELECT DISTINCT TYPE,
SUM(A) OVER(PARTITION BY TYPE) AS A,
FIRST_VALUE(B) OVER(PARTITION BY TYPE ORDER BY C DESC) AS D,
MAX(C) OVER(PARTITION BY TYPE) AS C
FROM data;
Output:
| TYPE | A | D | C |
|---|---|---|---|
| aaa | 22 | 8 | 2022-05-16 00:00:00.000 |
| bbb | 16 | 3 | 2022-05-25 00:00:00.000 |
See this db<>fiddle demo.