CODEWITHSUNDEEP
phpmysql
Ricky Joe
Ricky Joe

Reputation: 13

get last value from GROUP BY

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.

Upvotes: 1

Views: 249

Answers (3)

Jon Marnock
Jon Marnock

Reputation: 3225

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.

Upvotes: 1

mdamia
mdamia

Reputation: 4557

Use order by desc limit 1 will the first element.

Example :

select*,sum(qty) as qtys from sometable GROUP BY name ORDER BY id desc LIMIT 1

Since the price is determined by the latest entry, you just need to select by name = 'hot dog' for example, sum qty, order by id desc and limit one result.

 select*,sum(qty)as qtys, price 
 fromsometable 
 where name ='Hot-Dog'  orderB by  id desc LIMIT 1

Hope this help.

Upvotes: 2

Buildersrejected
Buildersrejected

Reputation: 169

You should do ORDER BY ascending or descending in your case descending.

"SELECT *,sum(qty) as qtys FROM sometable GROUP BY name ORDER BY price DESC"

There is more flexibility. Click here for more info http://www.w3schools.com/sql/sql_orderby.asp

Upvotes: 1

Related Questions