Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Jonathan Hartmayer
5,626 PointsAverage cost of order
In order to get a true average cost per order, wouldn't we need to divide by the number of user purchases made? Suppose a customer made 2 orders on different days. All purchases in both orders would get added under the same user_id in the average.
So to get per order cost, would it look like this? SELECT AVG(cost) / COUNT(DISTINCT ordered_on) AS average, user_id FROM orders GROUP BY user_id;
1 Answer
Kyle Knapp
21,526 PointsAVG() and GROUP BY do this automatically. In this case, GROUP BY grabs all of the individual orders for each user, and AVG() sums the order prices, and divides the sum by the total number of orders for that user. In other words, there is no need for us to perform additional operations to sum the orders and divide by the number of orders, AVG() does the heavy lifting for us and returns the result we expect, the sum of all order prices for that particular user_id divided by the total number of orders for that user_id.
Jonathan Hartmayer
5,626 PointsJonathan Hartmayer
5,626 PointsI was assuming that one "order" contains multiple transactions. Say I bought 2 items in one order, the two transactions would appear as two different orders on the table. I want to group these 2 transactions into 1 order constituted by day (assuming 1 order made per day per user).
I see what you mean about the average. So in that case, I would instead need to specify my own Average equation if I wanted it to be by day. I would remove AVG and just do: cost / COUNT(DISTINCT ordered_on) ... GROUP BY user_id.