Can we use same aggregate function more than once on same table field or column using Different filter conditions?

Question

I want to use SUM() function on 'Amount' field in a query 4 times on a same field with different filters.

something like

SELECT Date1,CC,BU, SUM(Amount),SUM(Amount),SUM(Amount),SUM(Amount)
FROM MainTable<br>
GROUP BY CC,BU,Date1

Here

1st SUM(Amount) should be calculated when Mode='011' And Mode='012' from MainTable
2nd SUM(Amount) should be calculated when Mode like '03_' And Mode Like '05_' from MainTable
3rd SUM(Amount) should be calculated when Mode like '10_' from MainTable
4th SUM(Amount) should be calculated when (Mode !='011') and (Mode !='012') (Mode not Like '03_') and (Mode not Like '05_') from MainTable

How to make this happen? I have tried in many ways but couldn't get the result the way I wanted.
Please help me.
Thank you in advance.

Answer 1

You can use an aggregate function with a CASE:

SELECT Date1,
  CC,
  BU, 
  SUM(case when mode = '011' then Amount end) Mode011,
  SUM(case when mode = '012' then Amount end) Mode012,
  SUM(case when mode = '013' then Amount end) Mode013,
  SUM(case when mode = '014' then Amount end) Mode014
FROM MainTable
GROUP BY CC,BU,Date1;

Or you can use the PIVOT function:

select date1, CC, BU,
  [011] Mode011, 
  [012] Mode012, 
  [013] Mode013, 
  [014] Mode014
from
(
  select date1, CC, BU, mode, amount
  from maintable
) src
pivot
(
  sum(amount)
  for mode in ([011], [012], [013], [014])
) piv