sql - How to use CASE WHEN in group by

Question

I want to use group by for the table NRW_MONTH_DATA.

 SELECT  [OBJECT_ID]
      ,[YEAR_MONTH]
      ,[SELLING_AMOUNT] 
      ,[DEFAULT_SELLING_DATA]
      ,[LOCK_SELLING_AMOUNT]
      ,[RGCB]
      ,[ICKZ]
      ,[YCKZ]
 FROM [dbo].[NRW_MONTH_DATA] 

IF LOCK_SELLING_AMOUNT is 0 then group by OBJECT_ID and calculate the sum of [RGCB],[ICKZ] and [YCKZ]

SELECT @SELLING_AMOUNT=(ISNULL(SUM(YCKZ),0)+ISNULL(SUM(RGCB),0)+ ISNULL(SUM(ICKZ),0))
    FROM [dbo].[NRW_MONTH_DATA] 
    WHERE OBJECT_ID=@OBJECT_ID 
    AND YEAR_MONTH >=@SELLING_CENSUS_START_YM 
    AND YEAR_MONTH <=@SELLING_CENSUS_END_YM
    GROUP BY OBJECT_ID

Now I want to add a condition that if LOCK_SELLING_AMOUNT is 1 , I need to

SELECT @SELLING_AMOUNT=ISNULL(SUM(DEFAULT_SELLING_DATA),0)

ELSE use original result to calculate the sum of the 3 columns.

I use CASE WHEN but is seems that I could not use it in group by

SELECT @SELLING_AMOUNT=
        CASE LOCK_SELLING_AMOUNT WHEN 1 THEN SELLING_AMOUNT
        ELSE (ISNULL(SUM(YCKZ),0)+ISNULL(SUM(RGCB),0)+ ISNULL(SUM(ICKZ),0))
        END

The error is like

The column'dbo.NRW_MONTH_DATA.LOCK_SELLING_AMOUNT' in the select list is invalid because the column is not included in the aggregate function or GROUP BY clause.

Thank you in advance.

I need the group by to calculate the sum of them. Each row has an object_id and a LOCK_SELLING_AMOUNT and other columns for one month, I want to use group to calculate the sum during month span. It works well when I do not consider the LOCK_SELLING_AMOUNT

question from:https://stackoverflow.com/questions/65838584/how-to-use-case-when-in-group-by

Answer 1

First, you don't want GROUP BY. So just use:

SELECT @SELLING_WATER = (COALESCE(SUM(YCKZ), 0) + COALESCE(SUM(RGCB), 0)+ COALESCE(SUM(ICKZ), 0))
FROM [dbo].[NRW_MONTH_DATA] 
WHERE OBJECT_ID=@OBJECT_ID AND
      YEAR_MONTH >= @SELLING_CENSUS_START_YM 
      YEAR_MONTH <= @SELLING_CENSUS_END_YM;

Now, the problem is that a column can change values on different rows. So, what row does LOCK_SELLING_AMOUNT come from? We could assume it is the same on all rows. Or perhaps you want an aggregation function:

SELECT @SELLING_WATER = (CASE WHEN MAX(LOCK_SELLING_AMOUNT) = 1
                              THEN MAX(CASE WHEN LOCK_SELLING_AMOUNT = 1 THEN SELLING_AMOUNT END)
                              ELSE (COALESCE(SUM(YCKZ), 0) + COALESCE(SUM(RGCB), 0)+ COALESCE(SUM(ICKZ), 0))
                         END)
FROM [dbo].[NRW_MONTH_DATA] 
WHERE OBJECT_ID=@OBJECT_ID AND
      YEAR_MONTH >= @SELLING_CENSUS_START_YM 
      YEAR_MONTH <= @SELLING_CENSUS_END_YM;