Dingo Bruce Dingo Bruce - 3 years ago 161
MySQL Question

MySQL query to calculate latest average prices using data from multiple tables

I am working on a PHP and MySQL based system to organise products and expenses for a restaurant.

I have data organised in to four tables.

Items table

id | name
1 | Beer
2 | Vodka


Products table

id | item_id | name
1 | 1 | Budweiser
2 | 1 | Sam Adams
3 | 2 | Smirnoff
4 | 2 | Grey Goose


Supplier table

id | name
1 | Supplier 1
2 | Supplier 2


Expenses table

id | product_id | cost | quantity | supplier | date
1 | 1 | 2.99 | 1 | 1 | 2017-09-05
2 | 1 | 3.00 | 2 | 2 | 2017-09-10
3 | 1 | 2.50 | 1 | 1 | 2017-09-20
4 | 1 | 3.98 | 2 | 1 | 2017-09-22
5 | 1 | 4.00 | 1 | 2 | 2017-09-25
6 | 1 | 8.00 | 2 | 2 | 2017-09-27


I would like to write a MYSQL Query that can figure out the cheapest supplier of a specific product based on the average cost per item (cost/quantity) of the latest 3 entires in the expenses table (based on date).

Here's what I want to compute:

Supplier 1 last 3 entries - costs per unit of: 2.99, 2.50 and 1.99. Average = 2.49

Supplier 2 last 3 entries - costs per unit of: 1.50, 4.00 and 4.00. Average = 3.16

So the SQL should return that Supplier 1 is the cheapest option for Product 1 (Budweiser).

So far I have attempted this, but I am a bit lost and confused:

select * from products
INNER JOIN expenses
ON products.id = expenses.product
AND products.item = '1'
ORDER BY (expenses.cost/expenses.quantity)
LIMIT 3;


The output of this query is which is a long way from what I'm trying to figure out :(:

id | item_id | name | id | product_id | cost | quantity | supplier | date
1 | 1 |Budweiser| 2 | 1 | 3.00 | 2 | 2 | 2017-09-10
1 | 1 |Budweiser| 4 | 1 | 3.98 | 2 | 1 | 2017-09-22
1 | 1 |Budweiser| 3 | 1 | 2.50 | 1 | 1 | 2017-09-20


The output I am looking for based on the sample data would be:

cheapest_supplier
1

Answer Source

I would like to write a MYSQL Query that can figure out the cheapest supplier of a specific product based on the average cost per item (cost/quantity) of the latest 3 entires in the expenses table (based on date).

That would require a query that works with user variables to generate a ranking based on date. And only select the three last dates.

Query

SELECT 
 *
FROM (
   SELECT 
   * 
   , CASE 
       WHEN @supplier = supplier
       THEN @rank := @rank + 1 
       ELSE @rank := 1
     END 
      AS rank
   , @supplier := supplier 
   FROM 
     Expenses 
   CROSS JOIN (
    SELECT
       @supplier := NULL
     , @rank := 0
    ) 
    AS
      init_user_params  
   WHERE
      product_id = 1       
   ORDER BY
      supplier ASC 
    , DATE DESC   
)
 AS Expenses_ranked 
WHERE
  Expenses_ranked.rank <= 3

Result

    id  product_id  cost    quantity  supplier  date        @supplier := NULL  @rank := 0    rank  @supplier := supplier  
------  ----------  ------  --------  --------  ----------  -----------------  ----------  ------  -----------------------
     4           1  3.98           2         1  2017-09-22  (NULL)                      0       1                        1
     3           1  2.50           1         1  2017-09-20  (NULL)                      0       2                        1
     1           1  2.99           1         1  2017-09-05  (NULL)                      0       3                        1
     6           1  8.00           2         2  2017-09-27  (NULL)                      0       1                        2
     5           1  4.00           1         2  2017-09-25  (NULL)                      0       2                        2
     2           1  3.00           2         2  2017-09-10  (NULL)                      0       3                        2

Using that results to generate a avg list per supplier.

Query

SELECT 
   Expenses_ranked.supplier 
 , AVG(Expenses_ranked.cost / Expenses_ranked.quantity) AS AVG
FROM ( 

  SELECT 
   * 
   , CASE 
       WHEN @supplier = supplier
       THEN @rank := @rank + 1 
       ELSE @rank := 1
     END 
      AS rank
   , @supplier := supplier 
   FROM 
     Expenses 
   CROSS JOIN (
    SELECT
       @supplier := NULL
     , @rank := 0
    ) 
    AS
      init_user_params  
   WHERE
      product_id = 1       
   ORDER BY
      supplier ASC 
    , DATE DESC   
)
 AS Expenses_ranked 
WHERE
  Expenses_ranked.rank <= 3
GROUP BY
  Expenses_ranked.supplier 

Result

supplier  avg           
--------  --------------
       1  2.4933333333  
       2  3.1666666667  

Now we can use a simple ORDER BY [] ASC LIMIT 1 to get the cheapest supplier

Query

SELECT 
 Expenses_ranked_avg.supplier AS cheapest_supplier
FROM ( 

  SELECT 
     Expenses_ranked.supplier 
   , AVG(Expenses_ranked.cost / Expenses_ranked.quantity) AS AVG
  FROM ( 

    SELECT 
     * 
     , CASE 
         WHEN @supplier = supplier
         THEN @rank := @rank + 1 
         ELSE @rank := 1
       END 
      AS rank
   , @supplier := supplier 
   FROM 
     Expenses 
   CROSS JOIN (
    SELECT
       @supplier := NULL
     , @rank := 0
    ) 
    AS
      init_user_params  
   WHERE
      product_id = 1       
   ORDER BY
      supplier ASC 
    , DATE DESC   
    )
      AS
        Expenses_ranked 
    WHERE
      Expenses_ranked.rank <= 3
    GROUP BY
      Expenses_ranked.supplier 
)
  AS Expenses_ranked_avg 
ORDER BY 
 Expenses_ranked_avg.avg ASC
LIMIT 1

Result

cheapest_supplier  
-------------------
                  1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download