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

Posted on 2012-09-14
Last Modified: 2012-09-16

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.
Question by:welcome 123
    LVL 9

    Expert Comment

    select SUM(Amount) as 'Tocompanytotal amount' from yourtablename
    LVL 26

    Expert Comment

    by:Alan Warren
    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
    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

    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

    Open in new window

    LVL 30

    Expert Comment


    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


    Author Comment

    by:welcome 123
    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?
    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.
    LVL 26

    Expert Comment

    by:Alan Warren
    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,

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    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.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now