SQL SELECT with multiple tables and SUM

Go To StackoverFlow.com

3

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

2012-04-04 18:23
by Hans Lernestål
You have two identical columns? SUM(product.location) | SUM(product.location)Nick Rolando 2012-04-04 18:26
Also, if source.location = product.location, why do you want a SUM on product_location - Michael Berkowski 2012-04-04 18:26
WHERE ???.location is *less than* 0 - Nick Rolando 2012-04-04 18:27
Shredder: I have updated. See above - Hans Lernestål 2012-04-04 18:32
possible duplicate of Simple SQL Select from 2 Tables (What is a Join?)John Conde 2012-06-17 23:35


4

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
2012-04-04 18:28
by Taryn
I 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) - Hans Lernestål 2012-04-04 18:40
is the query I suggested not giving you the result you want? if not, then what appears to be wrong - Taryn 2012-04-04 18:52
I get an error: #1054 - Unknown column 'source.location' in 'field list - Hans Lernestål 2012-04-04 18:57
can you post your query that you are running - Taryn 2012-04-04 19:01
SELECT s.sourceid , s.location , s.sourcename , s.sourcedescription , s.sourcedate , s.sourceprice , 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.sourceid , s.location , s.sourcename , s.sourcedescription , s.sourcedate , s.sourcepric - Hans Lernestål 2012-04-04 19:04
I 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 2012-04-04 19:05
are you using sql server - Taryn 2012-04-04 19:07
Yes, with phpMyAdmi - Hans Lernestål 2012-04-04 19:08
OK, 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 2012-04-04 19:27
updated my answe - Taryn 2012-04-04 19:47
Now the SQL goes through but with an empty result. Tricky - Hans Lernestål 2012-04-04 20:15
if you run the inner subquery, do you get results - Taryn 2012-04-04 20:23
Yes, i get a list from this query: SELECT location, sum(price) as Total FROM product WHERE quantity < 1 GROUP BY locatio - Hans Lernestål 2012-04-05 05:10
I 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 - Hans Lernestål 2012-04-05 05:33
The code (with a very minor change) is now working! Thx!! - Hans Lernestål 2012-04-05 07:10
if it helped, then consider accepting it as the answer via the checkmark on the left - Taryn 2012-04-05 10:19


0

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
2012-04-04 18:28
by Brian Hoover


0

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
2012-04-04 19:59
by GarethD
I 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 - Hans Lernestål 2012-04-05 05:28
Sorry, edited answer, it should now run - GarethD 2012-04-05 07:39
Ads