Nishant Bhuskade - 1 year ago 45

SQL 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)`

`Mode='011'`

`Mode='012'`

2nd

`SUM(Amount)`

`Mode like '03_'`

`Mode Like '05_'`

3rd

`SUM(Amount)`

`Mode like '10_'`

4th

`SUM(Amount)`

`(Mode !='011')`

`(Mode !='012')`

`(Mode not Like '03_')`

`(Mode not Like '05_')`

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 Source

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