joining multiple rows into one single row and get the total amount

Hello,

I need some help with sql:

I needs the total amount of the below rows in one single row:

ToCompany   FromCompany               Amount

A                                                        50,000

                              aa1                        35,000
                             bb1                        10,000
                             cc1                          5000

I want the result
as TOcompanytotal amount: 50,000 and fromCompanyTOtalamount: 50,000 for the result to be displayed in a table in my .netcode.
welcome 123Asked:
Who is Participating?
 
lwadwellCommented:
Do you mean something like:
SELECT SUM(CASE WHEN TOcompany IS NOT NULL THEN Amount ELSE 0 END) as TOcompanyTotal
     , SUM(CASE WHEN FromCompany IS NOT NULL THEN Amount ELSE 0 END) as FromCompanyTotal
FROM your_table

Open in new window

0
 
Vijaya Reddy Pinnapa ReddyCommented:
select SUM(Amount) as 'Tocompanytotal amount' from yourtablename
0
 
Alan WarrenApplications DeveloperCommented:
If you store the FromCompany data as an INT ID you could use a Pivot to return a single line summary:
Using PIVOT and UNPIVOT

SELECT [ToCompany], AmountReceived, [1] as 'From aa1', [2] as 'From bb1', [3] as 'From cc1'
FROM	(
		SELECT     R1.ToCompany, R2.FromCompany, R2.Amount,d.AmountReceived
		FROM         tblCompany_Receipts AS R1 INNER JOIN
							  tblCompany_Receipts AS R2 ON R1.ID = R2.ID inner join
								(SELECT ToCompany, SUM(Amount) AS AmountReceived FROM tblCompany_Receipts GROUP BY ToCompany) d on R1.ToCompany=d.ToCompany
		
		) p
PIVOT
(
sum([Amount])	
FOR FromCompany IN( [1],[2],[3])) AS pvt	

-- Returns:  
-- ToCompany	AmountReceived	From aa1	From bb1	From cc1
-- A	50000.00	35000.00	10000.00	5000.00

Open in new window

Alan
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
hnasrCommented:
Try:

select 'TOcompanytotal amount: ' + cast((select sum(Amount) from tbl As x where x.ToCompany   =z.ToCompany   and x.FromCompany Is Null) as nvarchar(25)) +' and fromCompanyTOtalamount: '+ cast((select sum(Amount) from tbl as y WHERE y.ToCompany   =z.ToCompany  and y.FromCompany is not null) as nvarchar(25))
from tbl As z
group by z.ToCompany   

Open in new window

0
 
welcome 123Author Commented:
I tried the solution given by Iwadwell and changed a tiny little bit which gave me the results I am expecting which is excellent, beautiful and gfantastic. actually i could not ask the question properly and the expertsa nd experts who understood what I want. I wish i can think like them one day... anyways I used the below:
SELECT SUM(CASE WHEN TOcompany=1 and fromcompany=0 THEN Amount ELSE 0 END) as TOcompanyTotal
     , SUM(CASE WHEN FromCompany=1 and tocompany=0 THEN Amount ELSE 0 END) as FromCompanyTotal
FROM your_table

I was little more careful and whentocompay is 1 then the table abviously has fromcompany as 0 but still gave like that for extra safety. Before I give my points to the above expert i want to ask him that the solution given by by: alanwarren is that the same ? I didn't even try as it looked a little complicated to me but if its the same I would ward my points 50% to both? kindly let me know?
0
 
lwadwellCommented:
Firstly, your changes make sense.  You did not show those columns in the original question - but I understand why you would use them.
As to the other answer ... it is a bit complicated and I think would produce a different result.  It seems to use columns not shown in the question too.
0
 
Alan WarrenApplications DeveloperCommented:
Hi guys,
glad you got a result; Just to clarify.

I was a little unsure of the table schema and how the data was stored.
Assumed there would be an ID field and that the data would be stored something like this:
ID      FromCompany      ToCompany      Amount
1      aa1      A      35000.00
2      bb1      A      10000.00
3      cc1      A      5000.00

ID being an Identity Seed of type INT from and to company fields being varchar(x) and the amount field being type money.

Then did a pivot on the ID field from a select that joined the table aliased as 'R1'  back to itself aliased as 'R2', which was in turn joined to a derived query that returned the TotalRecieved amount aliased as 'd' for derived. Was unsure of the actual table name, so called my version of the table tblCompany_Receipts.

Wasn't aware we had boolean fields in the picture, by which we could apply case logic.

Success with your app!

Respectfully yours,
Alan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.