Hi, this is Chris from Essential SQL and today we're going to go over the answers to the questions posed in the Group By blog
So the first question that was asked is, what is the average quantity ordered in the Order Details table
So there's the question. And the answer is simply to use an aggregate function across the whole table to achieve the desired answer
So we're doing select average of the quantity from Order Details. So the average quantity is 62.55
Alright, so the next question is, display the min-max and average quantity ordered for each product in Order Details
So when I see for each product in Order Details, that's a hint for me that I need to use Group By
So really what we're going to be doing is the min, the max, and average of quantity from Order Details and we're going to Group By Product ID
So I have a SQL statement here that does that. You can see where I said select Product ID, the min quantity, max quantity, and average from Order Details
We're grouping by the Product ID. So when I run that, I get the min, the max, and then the quantity
So the last question, which is a little harder, is return total sales by product for all orders but only include products included in seven or more Order Details
So let me paste this in so we can look at it. So return total sales, we know how to do that, that's the quantity times the price
By product, alright, that looks like we're going to do a Group By Product ID across all Order Details
So I'm really not using a WHERE clause. But only include products included on seven or more Order Details
Alright, so to me that's starting to hint towards a HAVING clause, right
Because I need to know seven or more means I'm going to have to do some kind of count
So I'm going to be doing a count on how many times it's in the group
So I can only do those types of things in a HAVING clause
So let me show you the SQL statement that I came up with
Let's see if you agree with me on this. You can follow along
You can see I've got Select Product ID, and here's the total sales, the unit price times quantity
From Order Details, again we're going to Group By Product ID, consider here by product
Having count of Product ID greater than seven. Seven or more, so this really should be greater than or equal to seven
Alright, so I caught a mistake I had made there. And when I run that, those are the results
If you have any questions about my answers, or have any other questions about SQL in general
you can tweet me at sqlchris, or leave a comment on my blog, and I'll get back to you
Have a great day