Query to select Sum of 3 Rows having maximum values in SQL Server

Last Reply 7 months ago By pandeyism

Posted 7 months ago

i have a pivoted table in which i have 4 columns and but i want to sum only those three columns which have highest value.

i want sum of rows but having max value.

CREATE TABLE [dbo].[test](
	[id] [int] NOT NULL,
	[1] [int] NULL,
	[2] [int] NULL,
	[3] [int] NULL,
	[4] [int] NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

 

You are viewing reply posted by: pandeyism 7 months ago.
Posted 7 months ago

Hi iammann,

Please refer below query.

SQL

DECLARE @testdemo AS TABLE(id int, [1] int ,[2] int, [3] int ,[4] int)
INSERT INTO @testdemo VALUES(1,21,31,43,13)
INSERT INTO @testdemo VALUES(2,12,22,25,23)
INSERT INTO @testdemo VALUES(3,51,44,24,55)
INSERT INTO @testdemo VALUES(4,10,04,30,85)

SELECT SUM(T1.[1])[1],SUM(T2.[2])[2],SUM(T3.[3])[3]
FROM (SELECT TOP 3 ROW_NUMBER() OVER (ORDER BY ([1]) DESC)row,[1] FROM @testdemo) AS T1
INNER JOIN (SELECT TOP 3 ROW_NUMBER() OVER (ORDER BY ([2]) DESC)row,[2] FROM @testdemo ) AS T2
ON T1.row = T2.row
INNER JOIN (SELECT TOP 3 ROW_NUMBER() OVER (ORDER BY ([3]) DESC)row,[3] FROM @testdemo) AS T3
ON T1.row = T3.row

Output

1 2 3
84 97 98