CODEWITHSUNDEEP
sqlsql-server
AnnaSwanna
AnnaSwanna

Reputation: 15

SQL Server query: Sum columns from 2 tables

I have these 3 tables:

What I need to do is sum the PRICE from PRODUCT table and the PRICE from the SERVICE_ table for each appointment/customer.

Here is what the result needs to look like: 

CUST_ID    final_price
------------
1234       45.16
4678       63.25
4587       78.58
7894       25.15

Upvotes: 0

Views: 49

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Assuming that prod_num and service_id are unique keys (or primary keys) in their respective tables, then aggregation is not necessary. To accomplish this you should use a left outer join, just in case some appointments don't have both fields:

select a.cust_id,
       (coalesce(p.price, 0) + coalesce(s.price, 0)) as Final_Price
from appointment a left outer join
     product p
     on p.prod_num = a.prod_num left outer join
     service s
     on s.service_id = a.service_id;

If there could be multiple rows in the reference tables for a given product or service, then you should do the aggregation before the join.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

SELECT   A.CUST_ID 
       , SUM(ISNULL(P.PRICE,0) + ISNULL(S.PRICE,0)) Final_Price
FROM APPOINTMENT A 
INNER JOIN PRODUCT P   ON A.PROD_NUM = P.PROD_NUM
INNER JOIN SERVICE S   ON S.SERVICE_ID = A.SERVICE_ID
GROUP BY A.CUST_ID

Upvotes: 0

Related Questions