Show lowest price but also group by date and sku

Question

I'm trying to find the lowest price of a selection of products from a MySql database and group by SKU and date and output them in to PHP.

I have looked for other answers but as far as i can see only group by 1 where as i firstly need to group all the products with a certain date and then group those by sku and display the lowest price for each sku

I have 1 table in the database which consists of

ID  | sku | company     | total_price | date
-------------------------------------------------
1   |tku1 | company_1   | 298.99      |2019/04/09
2   |tku1 | company_2   | 299.99      |2019/04/09
3   |tku1 | company_3   | 290.00      |2019/04/09
4   |stp1 | company_1   | 199.00      |2019/04/09
5   |stp1 | company_2   | 158.99      |2019/04/09
6   |stp1 | company_3   | 150.99      |2019/04/09
7   |upk1 | company_1   | 349.99      |2019/04/09
8   |upk1 | company_2   | 348.99      |2019/04/09
9   |upk1 | company_3   | 346.00      |2019/04/09
10  |dhu1 | company_1   | 520.99      |2019/04/09
11  |dhu1 | company_2   | 518.00      |2019/04/09
12  |dhu1 | company_2   | 515.99      |2019/04/09    
13  |tku1 | company_1   | 298.99      |2019/04/08
14  |tku1 | company_2   | 299.99      |2019/04/08
15  |tku1 | company_3   | 290.00      |2019/04/08
16  |stp1 | company_1   | 199.00      |2019/04/08
17  |stp1 | company_2   | 158.99      |2019/04/08
18  |stp1 | company_3   | 150.99      |2019/04/08
19  |upk1 | company_1   | 349.99      |2019/04/08
20  |upk1 | company_2   | 348.99      |2019/04/08
21  |upk1 | company_3   | 346.00      |2019/04/08
22  |dhu1 | company_1   | 520.99      |2019/04/08
23  |dhu1 | company_2   | 518.00      |2019/04/08
24  |dhu1 | company_2   | 515.99      |2019/04/08

This is what i have so far:-

$sql= ("SELECT * 
        FROM products 
        WHERE total_price = (
                SELECT MIN(total_price) 
                from products 
                GROUP BY sku 
                ORDER BY date");
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row['sku'] . "</td>";
echo "<td>" . $row['total_price'] . "</td>";
echo "<td>" . $row['company'] . "</td>";
echo "<td>" . $row['date'] . "</td>";
echo "</tr>";
}

but this doesn't work my goal is to display something like this in php

ID  | SKU | COMPANY     | TOTAL_PRICE | DATE
-------------------------------------------------
3   |tku1 | company_3   | 290.00      |2019/04/09
6   |stp1 | company_3   | 150.99      |2019/04/09
9   |upk1 | company_3   | 346.00      |2019/04/09
12  |dhu1 | company_2   | 515.99      |2019/04/09

What happens if there are two rows with same minimum price for a SKU. Which one would you show in that case ? — Madhur Bhaiya, Apr 09 '19 at 13:28
Good question but lets assume there are no 2 SKU's with the same price to keep it simple as i'm just trying to find the lowset price — infobuster, Apr 09 '19 at 13:31
@Nick has given the answer, which should work efficiently in your case. — Madhur Bhaiya, Apr 09 '19 at 13:33

score 1 · Accepted Answer · answered Apr 09 '19 at 13:31

1

The most efficient way to write this query is with a JOIN to a table of minimum total_price values for each sku:

SELECT p.* 
FROM products p
JOIN (SELECT sku, MIN(total_price) AS total_price
      FROM products 
      GROUP BY sku) mp ON mp.sku = p.sku AND mp.total_price = p.total_price
ORDER BY date

Output:

ID  sku     company     total_price date
3   tku1    company_3   290         2019-04-09
6   stp1    company_3   150.99      2019-04-09
9   upk1    company_3   346         2019-04-09
12  dhu1    company_2   515.99      2019-04-09

Demo on dbfiddle

answered Apr 09 '19 at 13:31

Nick

138,499
22
57
95

Although this does work it displays all the dates rather than just the selected date, i think i need to add in something like WHERE date = '2019-04-09' but i cant seem to figure out where it needs to go – infobuster Apr 09 '19 at 14:04
@infobuster sorry - you didn't have all the data in your question when I answered it. If you want a specific date, just replace `ORDER BY date` with e.g. `WHERE date='2019-04-09'` see the updated demo https://www.db-fiddle.com/f/jdozxEe8mdNuYGVHk6LYj/1 – Nick Apr 09 '19 at 14:07

Show lowest price but also group by date and sku

1 Answers1