Case sum in snowflake and name accordingly

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.

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;