CODEWITHSUNDEEP
sqlsql-server-2008
YVS1102
YVS1102

Reputation: 2748

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

Upvotes: 0

Views: 577

Answers (1)

sgeddes
sgeddes

Reputation: 62831

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)

Upvotes: 3

Related Questions