CODEWITHSUNDEEP
sql-server
Elameen Elsayed
Elameen Elsayed

Reputation: 23

Aggregate functions Min/Max Strange Output

I have the following code that displays strange output, I've previously thought MIN/MAX SHOULD display same results when there is only one record in the given group . The result for MONTH_2 and MONTH_4 is 0,0 for MIN 2,5 for MAX a clarification for this behavior would be appreciated

CREATE TABLE #CUST_ITEM (CUSTNMBR CHAR(31),ITEM CHAR(20),[MONTH] TINYINT,TOT_QTY DECIMAL(10,2))
INSERT INTO #CUST_ITEM VALUES('BA000057','BKNGPDR100',2,200)
INSERT INTO #CUST_ITEM VALUES('BA000057','BKNGPDR100',4,5000)
--QUERY_1 WITH AGGREGATE MIN
SELECT  CUSTNMBR,ITEM, 
MIN(CASE WHEN [MONTH] =2 THEN TOT_QTY ELSE 0 END) AS MONTH_2,
MIN(CASE WHEN [MONTH] =4 THEN TOT_QTY ELSE 0 END) AS MONTH_4,
MIN(CASE WHEN [MONTH] =5 THEN TOT_QTY ELSE 0 END) AS MONTH_5,
SUM(TOT_QTY) AS TOT_QTY_YEAR
FROM 
#CUST_ITEM
GROUP BY CUSTNMBR,ITEM
--QUERY_2 WITH AGGREGATE MAX
SELECT  CUSTNMBR,ITEM, 
MAX(CASE WHEN [MONTH] =2 THEN TOT_QTY ELSE 0 END) AS MONTH_2,
MAX(CASE WHEN [MONTH] =4 THEN TOT_QTY ELSE 0 END) AS MONTH_4,
MAX(CASE WHEN [MONTH] =5 THEN TOT_QTY ELSE 0 END) AS MONTH_5,
SUM(TOT_QTY) AS TOT_QTY_YEAR
FROM 
#CUST_ITEM
GROUP BY CUSTNMBR,ITEM

DROP TABLE #CUST_ITEM

Thanks in advance

Upvotes: 2

Views: 198

Answers (2)

JNK
JNK

Reputation: 65167

It's your case statements.

When you write

MIN(CASE WHEN [MONTH] =2 THEN TOT_QTY ELSE 0 END) AS MONTH_2,

It takes the MINIMUM value of either 0 or tot_qty, which is gonna be 0 unless tot_qty is negative.

Use NULL instead of 0, like:

SELECT  CUSTNMBR,ITEM, 
MIN(CASE WHEN [MONTH] =2 THEN TOT_QTY ELSE NULL END) AS MONTH_2,
MIN(CASE WHEN [MONTH] =4 THEN TOT_QTY ELSE NULL END) AS MONTH_4,
MIN(CASE WHEN [MONTH] =5 THEN TOT_QTY ELSE NULL END) AS MONTH_5,
SUM(TOT_QTY) AS TOT_QTY_YEAR
FROM 
#CUST_ITEM
GROUP BY CUSTNMBR, ITEM

NULL is ignored for aggregate functions which I think is what you want.

Upvotes: 1

Cyril Gandon
Cyril Gandon

Reputation: 17058

Well, 0 < 2,5, so...

It seems that you want to perform a pivot query. This is not by putting 0 in the row you didn't want, but null !

SELECT  CUSTNMBR,ITEM, 
MIN(CASE WHEN [MONTH] =2 THEN TOT_QTY ELSE null END) AS MONTH_2,
MIN(CASE WHEN [MONTH] =4 THEN TOT_QTY ELSE null END) AS MONTH_4,
MIN(CASE WHEN [MONTH] =5 THEN TOT_QTY ELSE null END) AS MONTH_5,
SUM(TOT_QTY) AS TOT_QTY_YEAR
FROM 
#CUST_ITEM
GROUP BY CUSTNMBR,ITEM

In this case, min or max are going to avoid the null value for doing their aggregate, whereas if you put 0, the min or max function will take the value !

Upvotes: 1

Related Questions