azraelGG Posted March 11, 2013 Share Posted March 11, 2013 i need calculate average of some data ill use simple example table so if you can help me. i will use only 2 users and 2 products for example it must be ordered by datetime(time in this table) and then get last 2 prices per user_ID calculate average table name: prices user_ID post_ID price time 1 1 10 13:00 - pick this row 1 1 11 12:00 1 1 12 14:00 - pick this row 3 1 10 13:00 - pick this row 3 1 11 12:00 3 1 12 14:00 - pick this row 1 2 15 13:00 - pick this row 1 2 16 12:00 1 2 17 14:00 - pick this row 3 2 18 13:00 - pick this row 3 2 19 12:00 3 2 12 14:00 - pick this row /* if there are more users get their last 2 entries */ or just how to query these "- pick this row" rest is not that hard final result post_ID total_avg_price 1 11 2 15.5 Link to comment https://forums.phpfreaks.com/topic/275484-calculating-average-advanced/ Share on other sites More sharing options...
Barand Posted March 11, 2013 Share Posted March 11, 2013 this should do it include("db_inc.php"); $db = new mysqli(HOST,USERNAME,PASSWORD,'test'); $db->query("DROP TABLE prices"); $sql = "CREATE TABLE prices ( user_ID INT, post_ID INT, price DECIMAL(10,2), time TIME, PRIMARY KEY (user_ID, post_ID, time) )"; $db->query($sql); $sql = "INSERT INTO prices VALUES (1 , 1 , 10.00 , '13:00:00'), (1 , 1 , 11.00 , '12:00:00'), (1 , 1 , 12.00 , '14:00:00'), (3 , 1 , 10.00 , '13:00:00'), (3 , 1 , 11.00 , '12:00:00'), (3 , 1 , 12.00 , '14:00:00'), (1 , 2 , 15.00 , '13:00:00'), (1 , 2 , 16.00 , '12:00:00'), (1 , 2 , 17.00 , '14:00:00'), (3 , 2 , 18.00 , '13:00:00'), (3 , 2 , 19.00 , '12:00:00'), (3 , 2 , 12.00 , '14:00:00')"; $db->query($sql); $sql = "SELECT post_ID, AVG(price) FROM ( SELECT price, time, @row := IF(@prevu <> user_ID OR @prevp <> post_ID, 1, @row+1) as row, @prevu := user_ID as user_ID, @prevp := post_ID as post_ID FROM prices JOIN (SELECT @row:=0, @prevu:=0, @prevp:=0) as init ORDER BY post_ID, user_ID, time DESC ) calcrow WHERE row < 3 GROUP BY post_ID"; /** RESULTS ******** 1 | 11.0000 2 | 15.5000 *********************/ Link to comment https://forums.phpfreaks.com/topic/275484-calculating-average-advanced/#findComment-1417933 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.