Solved

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

Posted on 2012-09-14
471 Views
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
Question by:welcome 123

LVL 9

Expert Comment

select SUM(Amount) as 'Tocompanytotal amount' from yourtablename
0

LVL 26

Expert Comment

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	(
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
``````
Alan
0

LVL 25

Accepted Solution

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
``````
0

LVL 30

Expert Comment

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
``````
0

Author Comment

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

LVL 25

Expert Comment

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

LVL 26

Expert Comment

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.

Respectfully yours,
Alan
0

## Featured Post

### Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.