Menu
I am trying to get the average of the lowest 5 priced items, grouped by the username attached to them. However, the below query gives the average price for each user (which of course is the price), but I just want one answer returned.
SELECT AVG(price) FROM table WHERE price > '0' && item_id = '$id' GROUP BY username ORDER BY price ASC LIMIT 5 
380
You use the DISTINCT operator in the AVG function to calculate the average value of the distinct values. For example, if you have a set of values 1,1,2,3, the AVG function with DISTINCT operator will return 2 i.e., (1 + 2 + 3) / 3 .
In SQL, sometimes we need to find the average value of a column based on another column of the table such as finding the student-wise average marks scored by him/her in all the subjects. This involves the use of the GROUP BY clause along with the AGGREGATE function like AVG.
No, you can't LIMIT subqueries arbitrarily (you can do it to a limited extent in newer MySQLs, but not for 5 results per group). This is a groupwise-maximum type query, which is not trivial to do in SQL.
The average is simply calculated by summing the amounts and divide by the difference between min and max dates for each row.
I think this is what you're after:
SELECT AVG(items.price) FROM (SELECT t.price FROM TABLE t WHERE t.price > '0' AND t.item_id = '$id' ORDER BY t.price LIMIT 5) items It will return the average of the 5 lowest prices - a single answer.
91
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
