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

Posted by Raj.Trivedi under Sql Server on 3/31/2015 | Points: 10 | Views : 650 | Status : [Member] [MVP] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

Latest Posts