Коррелированный подзапрос mysql

Итак, у меня есть таблица с продуктами 

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

У меня есть таблица с продажами 

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

И у меня есть таблица покупок 

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

Поэтому я пытаюсь вычислить прибыль на основе средней стоимости, используя запрос 

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`

Но я не могу заставить его работать я получаю "среднюю цену" - это неизвестный столбец, как бы я правильно структурировал запрос

2 2
sql mysql aggregate-functions mysql-error-1054

2 ответа:

SQL не поддерживает ссылку на псевдоним столбца в том же предложении SELECT - вот почему ваш столбец average_price возвращает ошибку 1054. Вы должны либо выполнить любую операцию, которая вам нужна в подзапросе, производной таблице / встроенном представлении, либо повторно использовать базовую логику, когда это необходимо. Вот пример повторного использования логики: 

   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

Мой запрос использует синтаксис соединения ANSI-92, который я рекомендую по сравнению с синтаксисом ANSI-89, используемым вашим запросом. Смотрите этот вопрос более подробно.

2

Как вы попали на этот запрос? Он полностью выключен.. При написании запроса начните с малого, а затем создайте его. Запрос, который у вас сейчас есть, - это полный беспорядок и нигде не подходит к действительности, есть случайные скобки " через него.

Для начала используйте отступ, чтобы сделать запрос читаемым

    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

"но я не могу заставить его работать, я получаю "среднюю цену" - это неизвестный столбец, как бы я правильно структурировал запрос "

Что такое "средняя цена"? Как хотите, чтобы была рассчитана средняя цена? И то же самое для "средней стоимости"

-1