How to get the All the records from a Table while using the sum and other calculations

Question

How to get the All the records from a Table while using the sum and other calculations

SameerKKhan

SSC-Addicted

Points: 435
More actions
November 4, 2011 at 8:25 am

#245683

Hi,
I have two tables,
Table1 and Table2.
Table1 has two columns of my need Amount,BalanceAmt.
Table2 has one column of my need Amt(it represents Amount)
what i am doing is Im calculatin the sum() of all three columns
select sum (Amount)as Paid,sum(BalanceAmt)as Balance,
(select sum(Amt) from FeeChequeStatus where ProcessStatus='Cleared' and RegNo='Any RegNo')as Cheque,
sum(BalanceAmt)-(sum (Amount)+(select sum(Amt) from FeeChequeStatus where ProcessStatus='Cleared'
and RegNo='Any RegNo')) as Outstanding
from FeeRecieptMaster where RegNo='Any RegNo'
Here as im providing with the filter to fetch the sum of the Specified RegNo,but i want that rather than a Single record i should get the record of all the RegNo's that are present in my table.
Like suppose if there are two regNo's say REgNo 1,2
Then i should get the result as two rows which should have
Four Columns :
Amount Balance Cheque Outstanding
1 sum(Amount) sum(balanceAmt) sum(Amt) Formula
2 sum(Amount) sum(balanceAmt) sum(Amt) Formula
How can i achieve this,i dont want to use any for loop or PL/Sql concepts.
Hope made my question clear.
Please Guide.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 1

You may have to clarify what you need, as I am not certain about your exepected end result. I assume you want the "RegNo", along with the four calculated columns.

The other important criteria, in order to determine the SQL, is how many rows per RegNo are present in each table. I created a sample for you to review to give you an idea of what can be done. There are other alternatives, but it all depends on what and how these tables are populated. If you perform an inner or outer join and both tables (or FeeReceiptMaster) have multiple rows per "RegNo", then the SUM values will be incorrect.

The sample below uses an OUTER APPLY to return all rows from your FeeReceiptMaster table and any matching rows from the FeeChequeStatus.

select frm.RegNo

, sum(frm.Amount)as Paid

, sum(frm.BalanceAmt)as Balance

, ISNULL(sum(ca.Amt),0)as Cheque

, sum(frm.BalanceAmt)-( sum (frm.Amount)+ sum(ISNULL(ca.Amt,0)) ) as Outstanding

from FeeRecieptMaster frm

outer apply (select SUM(fcs.Amt)as Amt from FeeChequeStatus fcs where fcs.RegNo=frm.RegNo and fcs.ProcessStatus='Cleared') ca

group by frm.RegNo

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Please provide a sample of the expected result along with sample data.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SameerKKhan SSC-Addicted Points: 435 More actions · Answer 3

Hi friends

thanks for the reply.

I have tried the query posted by John and giving me the expected result.

Im in the testing process of that,if required i would post my further issue on same Query,right now prior to testing process its looking great.

Thanks again for the help so quickly.:Wow:

SameerKKhan SSC-Addicted Points: 435 More actions · Answer 4

Hi frnds,

Im trying something like this for the same issue.

select BalanceAmt,(select Amt from FeeChequeStatus where ProcessStatus='Cleared' and RegNo=101106001)as ChequeAmt

,BalanceAmt- (select Amt from FeeChequeStatus where ProcessStatus='Cleared' and RegNo=101106001)

from FeeRecieptMaster where VoucherNo=(select max(VoucherNo) from FeeRecieptMaster where RegNo=Any RegNo)

and RegNo=Any RegNo

Please have a look,

here my table structure is such that the Max(voucherNo) in the FeeRecieptMaster has the latest BalanceAmt for that particular RegNo.

Now im taking that latest BalanceAmt and Substracting it from the sum(Amt) where ProcessStatus='Cleared' this is probably giving me the correct output.

My i want these results for all the regNo's and not only for the Provided regNo.

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 5

I would love to help, but do not quite understand what your expect from this new query. What is your desired output? Also, what are the table relationships between FeeChequeStatus and FeeReceiptMaster?

Check out the link for samples of how to post your questions a little better.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 6

After reviewing your SQL again, I can offer up a similar solution as what I previously provided. Without knowing much more, I cannot say this would provide the most optimal performance. But, it is my best guess based on your query.

This query sample is solely on the basis that you are trying to pull information for only 1 RegNo. The reason I used an "outer apply" instead of the subquery directly in the select clause was to perform the action once versus twice. This would make a bigger difference in large data sets.

select frm.BalanceAmt

, ISNULL(f.SumAmt, 0) as ChequeAmt

, frm.BalanceAmt - ISNULL(f.SumAmt,0)

from FeeRecieptMaster frm

OUTER APPLY (

select SumAmt=SUM(f.Amt)

from FeeChequeStatus f

where f.ProcessStatus='Cleared' and f.RegNo=frm.RegNo) fcs

where frm.VoucherNo=(select max(cfm.VoucherNo) from FeeRecieptMaster cfm where cfm.RegNo=frm.RegNo)

and frm.RegNo={Any RegNo}