Clever Geek Handbook

SQL SELECT with multiple tables and SUM

I have been using this forum for a while to find answers to some SQL related questions. Now is the time to ask the question that I have been trying to figure out for some time.

I have two tables (product and source).

I would like to create a SQL SELECT to retrieve a list of records from the source and one additional record from the product (SUM of prices). The table I would like to see should look something like this:

source.source_id | Source | source.source_name | source.source_description | source.source_date | source.price | SUM (product.price) | SUM (product.price) WHERE product.quantity <1 (this is the last column I get stuck in).

source.location and product.location are related.

This code works and gives the result that 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

Thanks bluefeet !!

+3


source to share


3 answers


without a lot of detail, you 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 request can be fine-tuned.



EDIT:

you can join the product 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
+4


source


I'm not sure I fully 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
0


source


From what I'm collecting, 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
0


source






More articles:


All Articles