asked
getting a sum of data from two rows of the same table
Hi there, I'm trying to get Profit/Loss result for my organization which have many branches. Here's a sample of result I've come up with: Lev1 B1 B2 B3 Total Income 999999 77777 55555 1133331 Expense -777777 -444444 -222222 -1444443 Now I want to get a final result as below: Lev1 B1 B2 B3 Total Income 999999 77777 55555 1133331 Expense -777777 -444444 -222222 -1444443 Total 222222 -366667 -166667 -311112 Now I'm having trouble visualizing a method to use the operator "sum" for the data in same table. I'd be grateful for your assistance. Thanks in prior.
I think you are trying to work with your data as if it was in a spreadsheet. You need to do the SUM in a different way, at a different time with a database. Can you provide any details of the base table where you are getting your first results set? You should be able to calculate the Total value at the same time.
Hi, I don't have clear idea about your requirement. However, i think you can create a table with 3 column like branch, Income, Expense. then insert you data into it. then run the below query: SELECT br, SUM(inc-exp) AS [Total Profit] FROM test GROUP BY br
Thanks for your interest. I sort it out by using the same temporary table as below: insert into #temp1 select *,'p/l'=t1.Total+t2.Total from #temp1 t1, #temp1 t2 where t1.Lev1='Income' and t2.Lev2='Expense'
