Hi, Lab4 Q3 states "Hint use a subquery to calculate the average quantity, you will need to use HAVING also (this is because in SQL the WHERE is applied before GROUP BY, meaning it cannot reference aggregate functions such as an average)" To get the expected answer I used WHERE instead of GROUP BY + HAVING clause. Am I missing something? Cheers

"Where" filters rows before any grouping or aggregation is done,you cannot use aggregate functions (like AVG, COUNT, etc.) directly in the "WHERE" clause because the aggregation happens after the filtering is done. "Having" is used specifically to filter groups created by GROUP BY based on aggregate functions. In Lab4 Q3 Having allows filtering based on the result of an aggregate function AVG(), because HAVING clause applies after the GROUP BY. So I don' think you can use Where instead of GROUP BY + HAVING in this case. Sometimes your query with WHERE might have returned the correct results, but the proper SQL structure should involve GROUP BY and HAVING in this case.

could you give a hint why we need to use GROUP BY ?

GROUP BY in SQL is to group rows that share a common value in one or more columns so that you can apply aggregate functions (like AVG(),COUNT(), etc.) on each group separately. Without GROUP BY, aggregate functions will operate on the entire result set,but you only want to calculate the average quantity for each product.

A practical example could be if you had a table of student table with names, degree, and grade, you want to get the average grade for each different degree the students are taking. You would add GROUP BY degree, so it would get the AVERAGE(grade) for each distinct degree, if you left out the group by, it would just average all of the students regardless of the degree. Hopefully that helps. ANONYMOUS wrote: