How can I get the MIN out of a SUM function?-CodePudding

I need to get two types of data within the SUM function: the MAX and the MIN.

The MAX function is easy, if I always do the sum and filter it by TOP 1 and desc, I get it correctly.

I try this to have the MAX, but how can I get the MIN function?

CodePudding user response：

You can use aggregation functions in the SUM():

SUM(MAX(col), MIN(col))

This adds the maximum value with the minimum value. In SQL Server, you can also use :

MAX(col)   MIN(col)

CodePudding user response：

Do you need to return 1 row for the group with the highest total and another row for the group with the lowest total?

Then, this should do the trick:

SELECT TOP 2
    Company
    ,CustomerCuttingOrder
    ,ManufacturingOrder
    ,SUM(customerAprWidth) as Total
    ,IIF(ROW_NUMBER() OVER (ORDER BY SUM(customerAprWidth) DESC) = 1, 1, 0)   AS isMax  -- you do not need this column in the results 
    ,IIF(ROW_NUMBER() OVER (ORDER BY SUM(customerAprWidth)) = 1, 1, 0)        AS isMin  -- you do not need this column in the results 
    
FROM ( VALUES 
    ('MCM', 1, 1368289,  10),
    ('MCM', 1, 1368289,  20),
    ('MCM', 1, 1368289,  30),
    ('MCM', 2, 1368289, 100),
    ('MCM', 2, 1368289, 200),
    ('MCM', 2, 1368289, 300),
    ('MCM', 3, 1368289,   1),
    ('MCM', 3, 1368289,   2),
    ('MCM', 3, 1368289,   3)
)tbl(Company, CustomerCuttingOrder, ManufacturingORder, customerAprWidth)
GROUP BY 
    Company
    ,CustomerCuttingOrder
    ,ManufacturingOrder
ORDER BY
    IIF(ROW_NUMBER() OVER (ORDER BY SUM(customerAprWidth) DESC) = 1, 1, 0) DESC  -- isMax
    ,IIF(ROW_NUMBER() OVER (ORDER BY SUM(customerAprWidth)) = 1, 1, 0)     DESC  -- isMin

Returns:

Company | CustomerCuttingOrder  | ManufacturingOrder  | Total
--------------------------------------------------------------
MCM     |       2               | 1368289             |  600
MCM     |       3               | 1368289             |  6