CODEWITHSUNDEEP
sqlsql-serverstored-procedures
DocuZach
DocuZach

Reputation: 73

Sum the values of multiple rows into a new column

I have a stored procedure returning a table that looks like this:

ID | Type | Price | Description
-------------------------------
2  | J    | 40.00 | Job
2  | F    | 10.00 | Freight
2  | P    | 20.00 | Postage
2  | F    |  5.00 | Handling
7  | J    | 50.00 | Job
7  | F    | 20.00 | Freight
7  | P    | 30.00 | Postage
7  | H    |  5.00 | Handling

I would like it to return the table like this:

ID | Type | Price | Description | FreightPrice
-----------------------------------------
2  | J    | 40.00 | Job         | 15.00
2  | F    | 10.00 | Freight     | 15.00    
2  | P    | 20.00 | Postage     | 15.00
2  | F    |  5.00 | Freight     | 15.00
7  | J    | 50.00 | Job         | 20.00
7  | F    | 20.00 | Freight     | 20.00
7  | P    | 30.00 | Postage     | 20.00
7  | H    |  5.00 | Handling    | 20.00

The intended result would sum the total freight cost per unique ID into its own column i.e. ID#2 total FreightPrice would be $15.00.

I have tried using the following to get that result

FreightPrice = (SELECT SUM(Price) 
                FROM Table
                WHERE Type = 'F' AND ID >= 2 AND ID <= 7)

The issue with this is that it returns a FreightPrice of $35.00 becuase it sums all instances of Type 'F'.

Do I need to separate these into two different subqueries or does anyone know of a way to achieve this with one subquery?

Upvotes: 3

Views: 64

Answers (2)

Zhorov
Zhorov

Reputation: 29943

Window function is better approach, but you may also try the following:

Input:

CREATE TABLE #Data (
   ID int,
   [Type] varchar(1),
   Price numeric(10, 2),
   Description varchar(50)
)
INSERT INTO #Data
   (ID, [Type], Price, Description)
VALUES   
   (2, 'J', 40.00, 'Job'),
   (2, 'F', 10.00, 'Freight'),
   (2, 'P', 20.00, 'Postage'),
   (2, 'F',  5.00, 'Handling'),
   (7, 'J', 50.00, 'Job'),
   (7, 'F', 20.00, 'Freight'),
   (7, 'P', 30.00, 'Postage'),
   (7, 'H',  5.00, 'Handling')

Statement:

SELECT t.*, s.*
FROM #Data t
LEFT JOIN (
   SELECT ID, SUM(Price) AS FreightPrice 
   FROM #Data 
   WHERE [Type] = 'F' 
   GROUP BY [ID]
) s ON (t.ID = s.ID)

Output:

ID  Type    Price   Description ID  FreightPrice
2   J       40.00   Job         2   15.00
2   F       10.00   Freight     2   15.00
2   P       20.00   Postage     2   15.00
2   F       5.00    Handling    2   15.00
7   J       50.00   Job         7   20.00
7   F       20.00   Freight     7   20.00
7   P       30.00   Postage     7   20.00
7   H       5.00    Handling    7   20.00

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to want a window function:

select t.*,
       sum(case when description = 'freight' then price end) over (partition by id) as freightprice
from t;

Upvotes: 4

Related Questions