title: How to calculate price average while grouping by date in MySQL?
body: <p>In MySQL 5.7 I need to calculate the average discount per merchant and day.</p>
  <p>There are 2 tables:</p>
  <p>produts_manufacturers:</p>
  <pre><code>PROD_ID | MANUFACTURER_ID | PRICE_RECOM
  1         1                 10
  2         1                 20
  </code></pre>
  <p>merchants_prices</p>
  <pre><code>PROD_ID | MERCHANT_ID | PRICE_ACTUAL | DATE
  1        10            9.00           21-01-20
  1        11            8.80           21-01-20
  1        11            9.00           22-01-19
  </code></pre>
  <p>My goal is a chart that gets its metric value from  <code>group by DATE, merchant_id</code></p>
  <p>Now how is it possible to calculate the average discount of all products from a manufacturer at a particular merchant without also grouping by PROD_ID?</p>
  <pre><code>   SELECT
          date,
          merchant_id,
          1- (t.PRICE_ACTUAL / p.PRICE_RECOM)*100 AS discount2
          -- (1 - ROUND(AVG(PRICE_ACTUAL),2) / p.PRICE_RECOM)*100 AS discount
      FROM
          merchants_prices t
          INNER JOIN produts_manufacturers p ON t.PROD_ID = p.PROD_ID
      WHERE
            p.MANUFACTURER_ID = 1
          AND PRICE_ACTUAL IS NOT NULL
      GROUP by
          date,
          MERCHANT_ID
      ORDER BY
         date desc,  merchant_id
  </code></pre>