YVS1102 YVS1102 - 5 months ago 7
SQL Question

Sum multiple columns grouped by month

Ok let me show my table first.

My columns:

Outlet | tanggal | cash | cc | dc | flash | piutang | reject | disc50


What I have done is sum
per day
. My query so far

select
Outlet, tanggal,
(cash + cc + dc + flash + piutang + reject + disc50) total
from
transaksi
where
outlet = 'K-AEON'


This query returns this result:

Outlet tanggal total
---------------------------
K-AEON 2016-01-22 2343300
K-AEON 2016-01-23 6091200
K-AEON 2016-01-24 6244700
K-AEON 2016-01-26 3284100
K-AEON 2016-01-25 1904600
K-AEON 2016-01-27 2743500


What I want is the result from above is
group by
with
month(tanggal)
.

So far I've tried this

select
Outlet, month(tanggal),
(cash + cc + dc + flash + piutang + reject + disc50) total
from
transaksi
where
outlet = 'K-AEON'
group by
month(tanggal), Outlet, cash, cc, dc, flash, piutang, reject, disc50


and the result

Outlet month total
----------------------
K-AEON 1 811100
K-AEON 1 1220850
K-AEON 1 1169700
K-AEON 1 1147500
K-AEON 1 1290000


My desired result is

Outlet month total
----------------------------------------
K-AEON 1 10000000 // all of January
K-AEON 2 20000000 // All feb
K-AEON 3 30000000 // All march
K-AEON 4 40000000 // All April
K-AEON 5 50000000 // All may


Any help ? Sorry for my bad English

Answer

You were close, but you need to use sum and then exclude those fields from the group by:

select Outlet, month(tanggal),
       sum(cash + cc + dc + flash + piutang + reject + disc50) total 
from transaksi 
where outlet = 'K-AEON' 
group by Outlet, month(tanggal)