asked on

How to include different total same row in SQL script

We have a table that with 3 columns: DocContract, Provider and Amount.

The there different types of Provider.

Sample of data:

DocContract   Provider	AMOUNT
Contract1     IBM       3.1
Contract1     TWA       15
Contract1     TWA       2.25
Contract2     IBM       1.1
Contract2     IBM       3.6
Contract2     IBM       1.1
Contract2     TWA       1.1
Contract2     TWA       1.1

Open in new window

We want the display to be:

Contract    IBM  TWA
Contract1   3.1  17.25
Contract2   5.8  2.2

Open in new window

So far this is what we have:

SELECT DocContract, (select sum(Amount) FROM @Sales s1 WHERE s1.Provider='TWA') 'TWA',
      (select sum(s2.Amount) FROM @Sales s2 WHERE s2.Provider='IBM' AND s2.Provider=s1.Provider) 'IBM'
      FROM @Sales
      GROUP BY DocContract

Open in new window

Please advice.

jana

ASKER

Here is a sample data:

DECLARE @Sales TABLE(
        DocContract VARCHAR(20),
        Provider VARCHAR(20),
        AMOUNT FLOAT
)
INSERT INTO @Sales SELECT 'Contract1','TWA', 2.25 
INSERT INTO @Sales SELECT 'Contract1','IBM', 3.1 
INSERT INTO @Sales SELECT 'Contract1','TWA', 15 
INSERT INTO @Sales SELECT 'Contract2','IBM', 3.6 
INSERT INTO @Sales SELECT 'Contract2','IBM', 1.1 
INSERT INTO @Sales SELECT 'Contract2','TWA', 1.1 
INSERT INTO @Sales SELECT 'Contract2','TWA', 1.1 
INSERT INTO @Sales SELECT 'Contract2','IBM', 1.1 

select * from @sales 
ORDER BY 1,2

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jana

ASKER

Thanx Worked! (how can we have missed it)!

jana

ASKER

Thanx

PortletPaul

no problem, thank you