Getting group Sum from subquery

JaimeJegonia
JaimeJegonia used Ask the Experts™
on
I'm trying to get individual balance per CustomerID and this is the view that gives me balance per SInvoiceNo:

SELECT     S.TotalDue - SUM(ISNULL(AR.AmountPaidGood, 0)) - SUM(ISNULL(AR.Deduction, 0)) AS Balance, S.CustomerID
FROM         dbo.tblSI AS S LEFT OUTER JOIN
                          (SELECT     SInvoiceNo, AmountPaid, CASE WHEN PayTag IS NULL OR
                                                   PayTag = 'Good' THEN AmountPaid ELSE 0 END AS AmountPaidGood, Deduction
                            FROM          dbo.tblARPayDetail) AS AR ON S.SInvoiceNo = AR.SInvoiceNo
GROUP BY S.TotalDue, S.CustomerID
HAVING      (S.TotalDue - SUM(ISNULL(AR.AmountPaidGood, 0)) - SUM(ISNULL(AR.Deduction, 0)) > 0)

Any help would be greatly appreciated.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
this should do:
sq.CustomerID, sum(sq.Balance)
from (
SELECT     S.TotalDue - SUM(ISNULL(AR.AmountPaidGood, 0)) - SUM(ISNULL(AR.Deduction, 0)) AS Balance, S.CustomerID
FROM         dbo.tblSI AS S 
LEFT OUTER JOIN
                          (SELECT     SInvoiceNo, AmountPaid, CASE WHEN PayTag IS NULL OR
                                                   PayTag = 'Good' THEN AmountPaid ELSE 0 END AS AmountPaidGood, Deduction
                            FROM          dbo.tblARPayDetail
      ) AS AR ON S.SInvoiceNo = AR.SInvoiceNo
GROUP BY S.SInvoiceNo, S.TotalDue, S.CustomerID
) sq
group by sq.CustomerID
HAVING      sum(sq.Balance) > 0

Open in new window

Author

Commented:
Brilliant! Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial