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!!
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
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
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
SUM(product.location) | SUM(product.location)
Nick Rolando 2012-04-04 18:26