CODEWITHSUNDEEP
sql
Connor Meek
Connor Meek

Reputation: 93

totaling one column with repeating values from others

I am having trouble totaling up the salaries cost in the database, it appears that the table contains other data that makes to so total the netamount for the year comes to total individual lines for each entry in the account. I'm guessing here on this

i have tried a aggregate to sum the netamount for the fiscalyear, I'll need this done for the past two years. but first with just 2017

select account, sum(netamount ) as salaryTotal, fiscalyear 
from hargrade_projectlink.twgltransaction 
where account = 50005 and fiscalyear = 2017 
group by account, creditamount, debitamount, netamount, fiscalyear 
order by fiscalyear

expected:

accout    salaryTotal  fiscalyear
actnum     sumtotal     2017

resulting:

accout  salaryTotal   fiscalyear
actnum     sumtotal     2017
actnum     sumtotal     2017
actnum     sumtotal     2017
actnum     sumtotal     2017
actnum     sumtotal     2017
actnum     sumtotal     2017
actnum     sumtotal     2017
etc and so on

Upvotes: 1

Views: 38

Answers (3)

Zoom
Zoom

Reputation: 75

Try this

          select account, fiscalyear ,sum(netamount ) as salaryTotal
          from hargrade_projectlink.twgltransaction 
          where account = 50005 and fiscalyear = 2017 
          group by account, fiscalyear 
          order by fiscalyear

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I suspect you want for your ultimate solution conditional aggregation as in:

select account,
       sum(case when fiscalyear = 2017 then netamount end) as salaryTotal_2017,
       sum(case when fiscalyear = 2018 then netamount end) as salaryTotal_2018
from hargrade_projectlink.twgltransaction 
where account = 50005 
group by account;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

try like below, remove creditamount, debitamount, netamount from group by

select account, sum(netamount ) as salaryTotal, fiscalyear 
from hargrade_projectlink.twgltransaction 
where account = 50005 and fiscalyear = 2017 
group by account,  fiscalyear 
order by fiscalyear

Upvotes: 2

Related Questions