Nishant Bhuskade Nishant Bhuskade - 4 months ago 13
SQL Question

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

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

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