Correlated subquery mysql

Question

so i have a table with products

Product ID | Product Name
===========+===============
1          | Tissues
2          | Glass

I have a table with sales

Sale ID    | Product ID | Quantity | Price
===========+============+==========+=============
1          | 1          | 1        | 55
2          | 2          | 1        | 60

and i have a table of purchases

Batch ID | Total Value | Quantity | Product ID
=========+=============+==========+==================
1        | 100         | 100      | 1
2        | 10          | 50       | 2
3        | 1           | 1        | 2

So im trying to calculate the profit based on average cost using the query

SELECT tblsale.product_id, 
       tblproduct.product_name, 
       SUM(tblsale.`quantity`) qty,
       SUM(tblsale.`Price`*tblsale.`quantity`) sales, 
       (SELECT sum(total_value) / sum(quantity) VWAP 
        FROM tblpurchases 
        WHERE product_id = tblsale.product_id) average_price, 
       (average_price * qty) cost, 
       (sales-cost) profit 
FROM   tblsale, tblproduct 
WHERE tblproduct.product_id = tblsale.`product_id` 
GROUP by tblsale.`product_id`

But i can't seem to get it to work i get a 'average price' is an unknown column, how would I structure the query correctly

Total value is the value of all the goods in the purchase and the quantity the number of units of the goods (so value/quantity) would be the average price of that particular batch — Tarang, Feb 12 '12 at 19:23

score 2 · Accepted Answer · edited May 23 '17 at 11:56

2

SQL doesn't support referencing a column alias in the same SELECT clause - that's why your average_price column is returning the 1054 error. You either have to do whatever operation you need in a subselect, derived table/inline view, or reuse the underlying logic where necessary. Here's an example of the reuse of logic:

   SELECT prod.product_id, 
          prod.product_name, 
          SUM(s.quantity) qty,
          SUM(s.Price * s.quantity) sales, 
          SUM(pur.total_value) / SUM(pur.quantity) average_price, 
          SUM(pur.total_value) / SUM(pur.quantity) * SUM(s.quantity) cost, 
          SUM(s.Price * s.quantity) - (SUM(pur.total_value) / SUM(pur.quantity) * SUM(s.quantity)) profit 
     FROM tblproduct prod 
LEFT JOIN tblsale s ON prod.product_id = s.product_id
LEFT JOIN tblpurchases pur ON pur.product_id = prod.product_id
 GROUP BY s.product_id

My query is using ANSI-92 JOIN syntax, which I recommend over the ANSI-89 syntax your query uses. See this question for more details.

edited May 23 '17 at 11:56

Community

1
1

answered Feb 12 '12 at 18:40

OMG Ponies

325,700
82
523
502

Hmm, I was wondering. Does this run faster than having the inner select? – Mosty Mostacho Feb 12 '12 at 18:43
@MostyMostacho: Subselect performance depends - on most databases, scalar ones perform equally. But others need to be checked, because they can execute row by row (not good). – OMG Ponies Feb 12 '12 at 18:45
Im getting a error 'GROUP BY s.product_id' at line 11' should the LEFT JOIN have an ON statement? – Tarang Feb 12 '12 at 19:23
ON (pur.product_id = s.product_id) ? – Tarang Feb 12 '12 at 19:28
You basically duplicated my answer, get the credits and I get `-1`. Something wrong here. – Richard Feb 12 '12 at 20:18
@Richard: There is no duplication - your answer uses a derived table/inline view, mine does not. As I said in my answer, either is valid. I don't feel that is enough to justify a downvote, personally. – OMG Ponies Feb 12 '12 at 21:15
@Akshat: Yes, sorry about that. I've corrected it - thx for letting me know. – OMG Ponies Feb 12 '12 at 21:15
"your answer uses a derived table/inline view" - You're looking at the wrong answer then. You answer is 99% the same as mine and yours was posted hours later. You should have rather commented on my answer than duplicating it. I.m.h.o. the downvote was righteous. – Richard Feb 13 '12 at 18:17
@Richard: You've edited your answer, but it still admonishes the OP and doesn't explain why the error occurs. Your answer had already been downvoted when I posted. Your downvote is clearly not righteous, but a matter of sour grapes -- you'd rather lash out at me than assess the entire content of my answer. I wonder if you're responsible for the other downvote I received today... – OMG Ponies Feb 14 '12 at 02:57

Richard · Answer 2 · 2012-02-12T19:00:09.350

How did you get to this query? It is completely off.. When writing a query, start small and then build it up. The query you have now is a complete mess and nowhere near to valid, there are random parenthesis' through out it.

To make a start, use indentation to make your query readable

    SELECT p.product_id, p.product_name
         , SUM(s.quantity) number_of_sales
         , SUM(s.price) total_profit
         , SUM(pu.quantity) purchase_quantity
         , SUM(pu.value) purchase_value
         , (SUM(pu.quantity) - SUM(s.quantity)) number_in_stock
         , (SUM(s.price) - SUM(pu.value)) profit
         , (SUM(pu.value) / SUM(pu.quantity)) avarage_purchase_price
      FROM product p
 LEFT JOIN sales s ON s.product_id = p.product_id
 LEFT JOIN purchase pu ON pu.product_id = p.product_id         
  GROUP BY s.product_id, pu.product_id

"But i can't seem to get it to work i get a 'average price' is an unknown column, how would I structure the query correctly"

What is 'average price'? How would you like average price to be calculated? And the same for 'average cost'

average cost and average price would be the same, average cost the weighted average cost of all the purchases on record — Tarang, Feb 12 '12 at 19:24

Correlated subquery mysql

2 Answers2