CODEWITHSUNDEEP
sqlsql-serversummaxssms
black31cat
black31cat

Reputation: 13

SQL Server SUM then MAX

How can I get the MAX value for the SUM range that I have?

Current output:

Size  |  Format | ColB   | Value
Large |  PCT    | Tmp1   | 23.05
Large |  PCT    | Tmp3   | 109.00
Large |  PCT    | Tmp50  | 3.22
Large |  PCT    | Tmp109 | 987.23
Large |  PCT    | Tmp58  | 455.30

Expected Output:

Size  |  Format | ColB   | Value  | Max_Value
Large |  PCT    | Tmp1   | 23.05  | 987.23
Large |  PCT    | Tmp3   | 109.00 | 987.23
Large |  PCT    | Tmp50  | 3.22   | 987.23
Large |  PCT    | Tmp109 | 987.23 | 987.23
Large |  PCT    | Tmp58  | 455.30 | 987.23

Let me know if this is possible.
Current code below:

USE DB1

DECLARE @StartDate DATE
DECLARE @EndDate DATE
DECLARE @ColA VARCHAR(10)
DECLARE @ColB VARCHAR(20)
DECLARE @ColC VARCHAR(15)
DECLARE @ColD VARCHAR(15)
DECLARE @ColE VARCHAR(15)
DECLARE @ColF VARCHAR(15)
DECLARE @ColG VARCHAR(15)

BEGIN
SET @StartDate = '01/01/2017'
SET @EndDate = '01/14/2017'
SET @ColA = '%%'
SET @ColB = '%%'
SET @ColC = '%%'
SET @ColD = '%%'
SET @ColE = '%%'
SET @ColF = '%%'
SET @ColG = '%%'
END

SELECT
    'Large' AS [Size]
    ,'PCT' AS [Format]
    ,[ColB]
    ,CAST((SUM([Numerator])/SUM([Denominator]))*100 AS NUMERIC(12,2)) AS [Value]
FROM [Table1] WITH(NOLOCK)
WHERE [Date] BETWEEN @StartDate AND @EndDate
AND [ColA] LIKE @ColA
AND [ColB] LIKE @ColB
AND [ColC] LIKE @ColC
AND [ColD] LIKE @ColD
AND [ColE] LIKE @ColE
AND [ColF] LIKE @ColF
AND [ColG] LIKE @ColG
GROUP BY [ColB]

Thank you in advance.

Upvotes: 1

Views: 152

Answers (2)

plamenbv
plamenbv

Reputation: 96

You can use a window function like this:

SELECT
    'Large' AS [Size]
    ,'PCT' AS [Format]
    ,[ColB]
    ,CAST((SUM([Numerator])/SUM([Denominator]))*100 AS NUMERIC(12,2)) AS [Value]
    ,cast(max(sum([Numerator])/sum([Denominator])) over(partition by null)) as numeric(12,2)) *100 as [MaxValue]
FROM [Table1] WITH(NOLOCK)
WHERE [Date] BETWEEN @StartDate AND @EndDate
AND [ColA] LIKE @ColA
AND [ColB] LIKE @ColB
AND [ColC] LIKE @ColC
AND [ColD] LIKE @ColD
AND [ColE] LIKE @ColE
AND [ColF] LIKE @ColF
AND [ColG] LIKE @ColG
GROUP BY [ColB]

I think it should work in your case.

Upvotes: 0

Matt
Matt

Reputation: 15071

Use a Sub query of your SUM and then use the MAX function to get this.

SELECT 'Large' AS [Size], 'PCT' AS [Format], [ColB],
CAST((SUM([Numerator])/SUM([Denominator]))*100 AS NUMERIC(12,2)) AS [Value],
    (SELECT MAX(SUBValue) 
    FROM (SELECT [ColB], CAST((SUM([Numerator])/SUM([Denominator]))*100 AS NUMERIC(12,2)) AS [SUBValue]
         FROM [Table1] WITH(NOLOCK)
         WHERE [Date] BETWEEN @StartDate AND @EndDate
         AND [ColA] LIKE @ColA
         AND [ColB] LIKE @ColB
         AND [ColC] LIKE @ColC
         AND [ColD] LIKE @ColD
         AND [ColE] LIKE @ColE
         AND [ColF] LIKE @ColF
         AND [ColG] LIKE @ColG
         GROUP BY [ColB])) AS Max_Value
FROM [Table1] WITH(NOLOCK)
WHERE [Date] BETWEEN @StartDate AND @EndDate
AND [ColA] LIKE @ColA
AND [ColB] LIKE @ColB
AND [ColC] LIKE @ColC
AND [ColD] LIKE @ColD
AND [ColE] LIKE @ColE
AND [ColF] LIKE @ColF
AND [ColG] LIKE @ColG
GROUP BY [ColB]

Upvotes: 1

Related Questions