SQL Group by Case Statement with Aggregate Function

SQL GROUP BY CASE statement with aggregate function

My guess is that you don't really want to GROUP BY some_product.

The answer to: "Is there a way to GROUP BY a column alias such as some_product in this case, or do I need to put this in a subquery and group on that?" is: You can not GROUP BY a column alias.

The SELECT clause, where column aliases are assigned, is not processed until after the GROUP BY clause. An inline view or common table expression (CTE) could be used to make the results available for grouping.

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 ... 

GROUP BY + CASE statement

Your query would work already - except that you are running into naming conflicts or just confusing the output column (the CASE expression) with source column result, which has different content.

...
GROUP BY model.name, attempt.type, attempt.result
...

You need to GROUP BY your CASE expression instead of your source column:

...
GROUP BY model.name, attempt.type
       , CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END
...

Or provide a column alias that's different from any column name in the FROM list - or else that column takes precedence:

SELECT ...
     , CASE WHEN attempt.result = 0 THEN 0 ELSE 1 END AS result1
...
GROUP BY model.name, attempt.type, result1
...

The SQL standard is rather peculiar in this respect. Quoting the manual here:

An output column's name can be used to refer to the column's value in
ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses;
there you must write out the expression instead.

And:

If an ORDER BY expression is a simple name that matches both an output
column name and an input column name, ORDER BY will interpret it as
the output column name. This is the opposite of the choice that GROUP BY
will make in the same situation. This inconsistency is made to be
compatible with the SQL standard.

Bold emphasis mine.

These conflicts can be avoided by using positional references (ordinal numbers) in GROUP BY and ORDER BY, referencing items in the SELECT list from left to right. See solution below.

The drawback is, that this may be harder to read and vulnerable to edits in the SELECT list (one might forget to adapt positional references accordingly).

But you do not have to add the column day to the GROUP BY clause, as long as it holds a constant value (CURRENT_DATE-1).

Rewritten and simplified with proper JOIN syntax and positional references it could look like this:

SELECT m.name
     , a.type
     , CASE WHEN a.result = 0 THEN 0 ELSE 1 END AS result
     , CURRENT_DATE - 1 AS day
     , count(*) AS ct
FROM   attempt    a
JOIN   prod_hw_id p USING (hard_id)
JOIN   model      m USING (model_id)
WHERE  ts >= '2013-11-06 00:00:00'  
AND    ts <  '2013-11-07 00:00:00'
GROUP  BY 1,2,3
ORDER  BY 1,2,3;

Also note that I am avoiding the column name time. That's a reserved word and should never be used as identifier. Besides, your "time" obviously is a timestamp or date, so that is rather misleading.

Using case with aggregate function & group by clauses

Try this:

SELECT
A
,B
,C
,SUM(CASE
     WHEN <COLUMN_NAME_A> IS NOT NULL 
     THEN <COLUMN_NAME_B> * <COLUMN_NAME_A>
          ELSE 0
END) AS <ALIAS>
FROM <TARGET_TABLE>
GROUP BY
A
,B
,C

But your CASE statement have no sense. If <COLUMN_NAME_A> is NULL i.e. <COLUMN_NAME_B> * <COLUMN_NAME_A> is NULL it will be ignored by SUM aggregate.
Use this instead.

SELECT
A
,B
,C
,ISNULL(SUM(<COLUMN_NAME_B> * <COLUMN_NAME_A>), 0) AS <ALIAS>
FROM <TARGET_TABLE>
GROUP BY
A
,B
,C

ISNULL used in case when no non-null value retrieved.

Aggregate in case statement with not a GROUP BY expression error

Here is your simplified example

create table  tab as
select 
1 SEGMENT1,
sysdate + rownum as CONTRACT_EFFECTIVE_DATE, rownum INVOICE_AMOUNT from dual 
connect by level <= 10;

select 
  SEGMENT1,
  case when sum(INVOICE_AMOUNT) > 50 and CONTRACT_EFFECTIVE_DATE > DATE'2020-01-01' then 'OK' else 'NOK' end as col1
from tab  
group by SEGMENT1;

This leads of course to an ORA-00979: not a GROUP BY expressionbecause you do not group by on CONTRACT_EFFECTIVE_DATE

But adding the CONTRACT_EFFECTIVE_DATE is most probably not what you want.

I guess you need to put the filter condition into the aggregate function, someting like this - so you aggregate INVOICE_AMOUNT only the if the condition is fullfiled and compares the result with the threshold

select 
  SEGMENT1,
  case when 
      sum(case when CONTRACT_EFFECTIVE_DATE > DATE'2020-01-01'then INVOICE_AMOUNT end) > 50  
  then 'OK' else 'NOK' end as col1
from tab  
group by SEGMENT1;

Error with aggregate function in a CASE statement and GROUP BY clause

I think you just want to aggregate by tenant_id and created_date:

 SELECT o.tenant_id as tenant_id,
        (CASE WHEN o.created_date >= MIN(mbpc.created_date) THEN true ELSE false
         END) as active
 FROM reporting.t_order o LEFT JOIN
      reporting.t_me_bill_pay_charge mbpc
      ON o.tenant_id = mbpc.tenant_id
 where o.retired_date is null
 and mbpc.retired_date is null
 group by o.tenant_id, o.created_date

---

Related Topics