SQL SELECT with multiple tables and SUM

select, sql

Solution

without a whole lot of details your can do something like this:

SELECT s.source_id
    , s.location
    , s.source_name
    , s.source_description
    , s.source_date
    , s.price
    , sum(p.price) as SumProductPrice
    , sum(p.location) as SumProductLocation
FROM source S
JOIN product p
    on S.location = p.location
WHERE p.quantity < 1
GROUP BY s.source_id, s.location, s.source_name
    , s.source_description, s.source_date, s.price

if you post more details, then the query can be fine-tuned.

EDIT:

you can join on the products table a second time to get the total for the location:

SELECT s.source_id
    , s.location
    , s.source_name
    , s.source_description
    , s.source_date
    , s.price
    , sum(p1.price) as SumProductPrice
    , p2.Total
FROM source S
JOIN product p1
    on S.location = p1.location
JOIN
(
    SELECT location, sum(price) as Total
    FROM product
    WHERE quantity < 1
    GROUP BY location
) p2
    on S.location = p2.location
WHERE p1.quantity < 1
GROUP BY s.source_id, s.location, s.source_name
    , s.source_description, s.source_date, s.price, p2.Total

Problem

I have used this forum for a while now to find answers to some SQL related questions. Now it's time to ask a question I have tried to figure out for some time now. I have two tables (product and source). I would like to create a SQL SELECT to retrieve a list of records from source and one additional record from product (a SUM of price). The table I would like to see should look something like this: source.source_id | source.location | source.source_name | source.source_description | source.source_date | source.price | SUM(product.price) | SUM(product.price) WHERE product.quantity < 1 (this last column is where I get stuck). source.location and product.location are linked. This code works and give the result I want: ``` SELECT s.source_id , s.location , s.source_name , s.source_description , s.source_date , s.source_price , p2.Total , sum(p1.price) as SumProductSold FROM source s JOIN product p1 on s.location = p1.location JOIN ( SELECT location, sum(price) as Total FROM product GROUP BY location ) p2 on s.location = p2.location WHERE p1.quantity < 1 GROUP BY s.source_id, s.location, s.source_name , s.source_description, s.source_date, s.source_price, p2.Total ``` Thank you bluefeet!!

Original source