I have some data like this:

I need to get an output like this:

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.

CodePudding user response：

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:

See this db<>fiddle demo.

CodePudding user response：

Here is one approach using ROW_NUMBER along with pivoting logic:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY C DESC) rn
    FROM data
)

SELECT TYPE, SUM(A) AS A,
       MAX(CASE WHEN rn = 1 THEN B END) AS D,
       MAX(CASE WHEN rn = 1 THEN C END) AS C
FROM cte
GROUP BY TYPE;

Here is a working demo.