Can I use a column dynamically using CASE WHEN in a GROUP BY query?

I have a column that looks something like this:

CASE
    WHEN col1 > col2 THEN SUM(col3*col4)
    ELSE 0
END AS some_product

Inline view:

select ...
from (select ... , CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product
   from ...
   group by col1, col2 ... ) T
group by some_product ...

CTE:

with T as (select ... , CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END AS some_product
   from ...
   group by col1, col2 ... )
select ...
from T
group by some_product ...

The simple solution is that you need to put your summation outside of the case statement. This should do the trick:

sum(CASE WHEN col1 > col2 THEN col3*col4 ELSE 0 END) AS some_product

write it as

Sum(CASE WHEN col1 > col2 THEN SUM(col3*col4) ELSE 0 END) as SumSomeProduct

For the next few lessons, you'll work with data on College Football Players. This data was collected from ESPN on January 15, 2014 from the rosters listed on this page using a Python scraper available here. In this particular lesson, you'll stick to roster information. This table is pretty self-explanatory—one row per player, with columns that describe attributes for that player. Run this query to check out the raw data:

SELECT * FROM benn.college_football_players

Every CASE statement must end with the END statement. The ELSE statement is optional, and provides a way to capture values not specified in the WHEN/THEN statements. CASE is easiest to understand in the context of an example:

SELECT player_name,
       year,
       CASE WHEN year = 'SR' THEN 'yes'
            ELSE NULL END AS is_a_senior
  FROM benn.college_football_players

But what if you don't want null values in the is_a_senior column? The following query replaces those nulls with "no":

SELECT player_name,
       year,
       CASE WHEN year = 'SR' THEN 'yes'
            ELSE 'no' END AS is_a_senior
  FROM benn.college_football_players

You can also define a number of outcomes in a CASE statement by including as many WHEN/THEN statements as you'd like:

SELECT player_name,
       weight,
       CASE WHEN weight > 250 THEN 'over 250'
            WHEN weight > 200 THEN '201-250'
            WHEN weight > 175 THEN '176-200'
            ELSE '175 or under' END AS weight_group
  FROM benn.college_football_players

While the above works, it's really best practice to create statements that don't overlap. WHEN weight > 250 and WHEN weight > 200 overlap for every value greater than 250, which is a little confusing. A better way to write the above would be:

SELECT player_name,
       weight,
       CASE WHEN weight > 250 THEN 'over 250'
            WHEN weight > 200 AND weight <= 250 THEN '201-250'
            WHEN weight > 175 AND weight <= 200 THEN '176-200'
            ELSE '175 or under' END AS weight_group
  FROM benn.college_football_players

For example, The query I was thinking of is as below,

SELECT DATE, A,B,C FROM TABLE
GROUP BY DATE
   CASE WHEN C ='1' THEN A,B
ELSE '2' THEN A END.

If C is 1, the final query is

SELECT DATE, A,B,C FROM TABLE
GROUP BY DATE, A, B

If C is 2, the final query is

SELECT DATE, A,B,C FROM TABLE
    GROUP BY DATE, A

The query is applied like this according to each C value, so the result I want is

DATE     A       B       C
----    ------------------
2022.01 A1       50      1
2022.01 A2       30      1
2022.02 (A1+A2)  80      2
2022.03 A1       10      1
2022.03 A2       20      1

You might be able to get the result you want by combining the result of two queries using UNION ALL. The exact syntax may differ (sometimes you have to select from a subquery) and you may want to specify ordering but I think this could work.

SELECT DATE,A,B,C 
FROM TABLE
 WHERE C = '1'
 GROUP BY DATE,A,B
UNION ALL
SELECT DATE,A,B,C 
 FROM TABLE
 WHERE C = '2'
GROUP BY DATE,A;