CODEWITHSUNDEEP
sqlsql-server
James Wilson
James Wilson

Reputation: 5150

Sum a specific column

I need to sum the data of a column only when the payment date is the same.

Here is the data returned:

judgment    PaymentDate    interestrate  current1  paymentamount  principalamount
2/1/2008    7/31/2010       9.00          5781.04   -315.07        -270.07
2/1/2008    7/31/2010       9.00          5781.04    272.59         227.59

Here is the query:

select tb1.judgment, fulldate as PaymentDate, m.interestrate, m.current1, paymentamount, principalamount from master m
            inner join AARS_JudgmentsWithPA tb1 on tb1.jmacct = m.number
            inner join courtcases cc on cc.accountid = m.number
            where m.lastinterest != '2099-01-01 00:00:00.000'
            and tb1.fulldate > tb1.judgment
            and cast(tb1.PrincipalAmount as money) != 0
            and tb1.judgment != ''
            and m.number = 568463

For the example data provided I would need those rows combined because the payment date is the same. So payment amount should be condensed down to be (-270.07 + 227.59) which is -42.48.

I tried to group by on fulldate and sum payment amount but it wanted me to group on every column which then returned both rows again.

What am I missing?

Upvotes: 0

Views: 82

Answers (2)

D Stanley
D Stanley

Reputation: 152634

You need to speficy an aggregation for columns that you are not grouping by. For numeric columns that repeat MIN(), MAX() or AVG() would be appropriate. For string columns MIN() or MAX() would work. 

select tb1.judgment, 
       fulldate as PaymentDate, 
       MIN(m.interestrate) AS interestrate, 
       MIN(m.current1) AS current1,
       SUM(paymentamount) AS paymentamount, 
       SUM(principalamount) AS principalamount
from master m
inner join AARS_JudgmentsWithPA tb1 on tb1.jmacct = m.number
inner join courtcases cc on cc.accountid = m.number
where m.lastinterest != '2099-01-01 00:00:00.000'
    and tb1.fulldate > tb1.judgment
        and cast(tb1.PrincipalAmount as money) != 0
        and tb1.judgment != ''
        and m.number = 568463
group by tbl.judgment, fulldate

Another option would be to group on columns that repeat and aggregate the rest:

select tb1.judgment, 
       fulldate as PaymentDate, 
       m.interestrate, 
       m.current1, 
       SUM(paymentamount) AS paymentamount, 
       SUM(principalamount) AS principalamount
from master m
inner join AARS_JudgmentsWithPA tb1 on tb1.jmacct = m.number
inner join courtcases cc on cc.accountid = m.number
where m.lastinterest != '2099-01-01 00:00:00.000'
    and tb1.fulldate > tb1.judgment
        and cast(tb1.PrincipalAmount as money) != 0
        and tb1.judgment != ''
        and m.number = 568463
group by tbl.judgment, fulldate, m.interestrate, m.current1

Upvotes: 1

the_pete
the_pete

Reputation: 822

You are missing nothing, when you are using an aggregate function, in this case sum(), you have to group by every other non-aggregate column. The first few sentenced explain in a pretty verbose fashion: http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html and a list of functions that require you to group by: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Upvotes: 0

Related Questions