Case sum in snowflake and name accordingly

Question

I have joined multiple views to get the following output for time taken by each Department to complete a task. The output looks like this:

ID	PROCESS	DEPT	SECONDS
S911	BATCH	SALES	24268
S911	BATCH	MKTG	2992
S911	BATCH	HR	77
S911	BATCH	FIN	14995
S911	BATCH	DATA	739
S911	BATCH	ITA	8988

I need the output to be this:

ID	PROCESS	DEPT_2	SECONDS
S911	BATCH	EXEMPT	24345
S911	BATCH	NONEXEMPT	24722
S911	BATCH	OTHERS	2992

Can this possibly done with a case statement where exempt is sum of sales and HR non exempt is sum of finance data and product and of there are any other departments they need to be summed up as others.

Answer 1

Yes you can use a case expression in combination with an aggregation.

I don't use Snowflake but I believe you can use a column alias in a group-by clause, which would look as follows, where t is a derived table or common table expression for your existing query:

select Id, Process,
  case 
    when dept in ('SALES', 'HR') then 'EXEMPT'
    when dept in ('FIN', 'DATA', 'ITA') then 'NONEXEMPT'
    else 'OTHERS'
  end Dept_2,
  Sum(Seconds) Seconds
from t
group by Id, Process, Dept_2
order by Dept_2;