CODEWITHSUNDEEP
mysqlsummin
capte
capte

Reputation: 207

MySQL SUM() Subquery with MIN()

First of all here what i'm trying to accomplish:

Get the sum of etp_product.price and option_price.

SELECT 
  etp_product.product_id,
  etp_product.price,
  (SELECT MIN(price) AS FIELD_2 FROM etp_product_option_value pov WHERE pov.product_id = etp_product.product_id) AS option_price
FROM
  etp_product
GROUP BY
  etp_product.product_id,
  etp_product.price

I've tried this: 

SELECT 
  etp_product.product_id,
  etp_product.price,
  (SELECT MIN(price) AS FIELD_2 FROM etp_product_option_value pov WHERE pov.product_id = etp_product.product_id) AS option_price,
SUM(etp_product.price + option_price) AS Total
FROM
  etp_product
GROUP BY
  etp_product.product_id,
  etp_product.price

But get this error:

Unknown column 'option_price' in 'field list'

Any Idea how it could be done ?

Upvotes: 0

Views: 1985

Answers (2)

newtover
newtover

Reputation: 32094

SELECT 
  p.product_id,
  p.price,
  IFNULL(MIN(pv.price), 0) as option_price,
  (p.price + IFNULL(MIN(pv.price), 0)) as total
FROM etp_product p
JOIN etp_product_option_value pv ON WHERE pv.product_id = p.product_id
GROUP BY
  p.product_id,
  p.price

Upvotes: 2

Bert
Bert

Reputation: 82469

SELECT 
etp_product.product_id,
  etp_product.price,
  (SELECT MIN(price) AS FIELD_2 FROM etp_product_option_value pov WHERE pov.product_id = etp_product.product_id) AS option_price,
 etp_product.price + (SELECT MIN(price) AS FIELD_3 FROM etp_product_option_value pov WHERE pov.product_id = etp_product.product_id) AS Total
FROM
  etp_product
GROUP BY
  etp_product.product_id,
  etp_product.price

Upvotes: 0

Related Questions