SQL SELECT with multiple tables and SUM
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
Hans Lernestål
I am managing the website of Online Philately - www.onlinephilately.com Am responsible for the coding and the development of the site and the admin.
Updated on June 06, 2022Comments
-
Hans Lernestål almost 2 years
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!!
-
Hans Lernestål about 12 yearsI am storing information on purchased products in my source table. I am able to list all the information from this table but I would like to have an additional column (the SUM of sold product).The link is the location id (source.location and product.location).
-
Taryn about 12 yearsis the query I suggested not giving you the result you want? if not, then what appears to be wrong?
-
Hans Lernestål about 12 yearsI get an error: #1054 - Unknown column 'source.location' in 'field list'
-
Taryn about 12 yearscan you post your query that you are running?
-
Hans Lernestål about 12 yearsSELECT s.source_id , s.location , s.source_name , s.source_description , s.source_date , s.source_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.source_price
-
Hans Lernestål about 12 yearsI get a list now, but the last column is not correct yet. It should be showing the sum of the sold products that are connected to the location/source. Thanks for your input.
-
Hans Lernestål about 12 yearsOK, but I get an error #1054 - Unknown column 't.price' in 'field list'. Is this the product.price or the source.price?
-
Hans Lernestål about 12 yearsNow the SQL goes through but with an empty result. Tricky.
-
Taryn about 12 yearsif you run the inner subquery, do you get results?
-
Hans Lernestål about 12 yearsYes, i get a list from this query: SELECT location, sum(price) as Total FROM product WHERE quantity < 1 GROUP BY location
-
Hans Lernestål about 12 yearsI get the flowing error on this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Price1], SUM(CASE WHEN product.quantity < 1 THEN product.price ELSE 0 E' at line 7
-
Hans Lernestål about 12 yearsI have tried the whole string again and SumProductTotal and Total are the same. One should be the total of all products with a specific location and one should be the total of all products with quantity <1 with the same location.