CODEWITHSUNDEEP
sqlselect
Hans Lernestål
Hans Lernestål

Reputation: 85

SQL SELECT with multiple tables and SUM

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!!

Upvotes: 3

Views: 17515

Answers (3)

GarethD
GarethD

Reputation: 69769

From what I gather you want something like this:

SELECT  source.source_id,
        source.location,
        source.source_name,
        source.source_description,
        source.source_date,
        source.price,
        SUM(product.price) AS Price1,
        SUM(CASE WHEN product.quantity < 1 THEN product.price ELSE 0 END)  AS Price2
FROM    Source
        INNER JOIN Product
            ON Product.Location = Source.Location
GROUP BY 
        source.source_id,
        source.location,
        source.source_name,
        source.source_description,
        source.source_date,
        source.price

Upvotes: 0

Taryn
Taryn

Reputation: 247700

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

Upvotes: 4

Brian Hoover
Brian Hoover

Reputation: 7991

I'm not sure I completely understand your table structure, but something like this should work:

Select source.source_id, 
  source.location, 
  source.source_name, 
  source.source_description,
  source.source_date,
  source.price,
  sum(production.location)
from source, product
  where source.location = production.location
  and location < 0
group by source.source_id, 
  source.location, 
  source.source_name, 
  source.source_description,
  source.source_date,
  source.price

Upvotes: 0

Related Questions