Sum function not working for the below query. [Resolved]

Posted by Raj.Trivedi under Sql Server on 3/31/2015 | Points: 10 | Views : 395 | Status : [Member] [MVP] | Replies : 2
I am trying to get the sum but i am only getting the value of current paid amount when i order it by date desc order

select Top 1 TMN.Invoiceno,TMN.BillDate,PIM.CurrentPaidAmount,PIM.InvoiceAmount,CM.ClientName,PIM.PaymentDate,PIM.OutstandingAmount,
sum(PIM.CurrentPaidAmount) as TotalPaid from TransactionMasterNew TMN
inner join PaymentInfo PIM
on
TMN.Invoiceno = PIM.InvoiceNo
inner join
ClientMaster CM on
CM.ClientId = TMN.ClientId
where
TMN.Invoiceno = 'AC/012' and PIM.OutstandingAmount > 0.00
group by TMN.Invoiceno,CM.ClientName,TMN.BillDate,PIM.CurrentPaidAmount,PIM.InvoiceAmount,PIM.PaymentDate,PIM.OutstandingAmount
order by PIM.PaymentDate Desc

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved



Responses

Posted by: Bandi on: 4/1/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Try this,

select Top 1 TMN.Invoiceno,TMN.BillDate,PIM.CurrentPaidAmount,PIM.InvoiceAmount,CM.ClientName,PIM.PaymentDate,PIM.OutstandingAmount,
sum(PIM.CurrentPaidAmount) over() as TotalPaid
from TransactionMasterNew TMN
inner join PaymentInfo PIM on TMN.Invoiceno = PIM.InvoiceNo
inner join ClientMaster CM on CM.ClientId = TMN.ClientId
where TMN.Invoiceno = 'AC/012' and PIM.OutstandingAmount > 0.00
order by PIM.PaymentDate Desc


If this is not you resultant output, what is the logic for TotalPaid Column. explain with clear sample data....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Raj.Trivedi on: 4/2/2015 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hello Bandi,

It worked fine can you explain why did you us over keyword for sum and what was wrong with my query

Regard's
Raj.Trivedi
"Sharing is Caring"
Please mark as answer if your Query is resolved

Raj.Trivedi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response