• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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.
0
welcome 123
Asked:
welcome 123
1 Solution
 
Vijaya Reddy Pinnapa ReddyCommented:
select SUM(Amount) as 'Tocompanytotal amount' from yourtablename
0
 
Alan WarrenCommented:
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
 
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 WarrenCommented:
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now