从GROUP BY获取最后一个值

Question

i have table like

id  Name       Taste      price        Qty
1   Hot-Dog    Spicy      500           3
2   Pop-Corn   Caramel    400           2
3   Hot-Dog    Non-Spicy  600           1      
4   Hot-Dog    Spicy      520           4     
5   Pop-Corn   Salty      350           5

how to make a return row

Name       Taste      price    Qty
Hot-Dog    Spicy      520       7  <--- Group by name and taste, sum(qty), with lastest price 
Pop-Corn   Caramel    400       2
Hot-Dog    Non-Spicy  600       1
Pop-Corn   Salty      350       5

$sql = mysqli_query($con,"SELECT *,sum(qty) as qtys FROM sometable GROUP BY name,taste");

simply word.. return group by name and taste with "lastest price(biggest id) from same item" and sum(qty)

how to get lastest price. i try min(),max() it return smallest or biggest value.

Answer 1

In MySQL you're allowed to refer to columns that aren't in the GROUP BY clause or inside an aggregate function, however as per the docs https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html the resulting value returned is indeterminate if they aren't all the same.

However, you can do the query in two steps: first, get the aggregate information, then join it back to the same table to get the price of the max id:

SELECT name, taste, price, qtys
FROM (SELECT MAX(id) AS maxid, SUM(qty) AS qtys FROM sometable GROUP BY name, taste) AS lnt
JOIN sometable AS st ON st.id = lnt.maxid
ORDER BY name, taste

Explanation of how this works:

Start with the inner query. In here, we're grouping the data by name and taste, and then getting the sum of quantities for each (name, taste) pair, and the max id (which was your "latest" entry you were after). We could also get the actual name and the taste values if we wanted to from here, but there's no point because we can get that later from the outer table (the max id is all the info we need to get the name and taste) and it simplifies the SQL a little.

Now the inner query will give you a list of unique ids and quantities. Each ID references a (name, taste) pair - specifically, the one with the largest ID for that (name, taste) combination. So, we join the inner query back to the sometable table, matching the maxid to the id fields.

Then it's just a simple matter of returning the name, taste, and price from sometable, and the qtys from the inner table, and you have everything you need - name, taste, "latest" price, and the sum of quantities.