March 11, 2008 at 11:56 pm
Hello All,
Again, I am a Newbie currently polishing up on my freshly completed training. Below is an explanation and example of a query:
The following code shows a GROUP BY clause with an aggregate. This example returns only the category name with the average price for the category:
Use Northwind
SELECT CategoryName, AVG(UnitPrice)AS 'Average'
FROM Categories cat JOIN Products pro
ON pro.CategoryID = cat.CategoryID
GROUP BY CategoryName
ORDER BY CategoryName
My questions are:
1) Is this query looking to gain an aggregate of the AVG of BOTH the Categories and Products table column CategoryID?
2) What exactly is "ON" for? Is this a direction to the compiler, and if so, what does it mean exactly?
3) Why is there both GROUP BY and ORDER BY? Is because the first is related to the JOIN, and the 2nd is related to the result set?
Let me know what you know, thanks...;)
March 12, 2008 at 12:04 am
1) Is this query looking to gain an aggregate of the AVG of BOTH the Categories and Products table column CategoryID?
Its doing an avg of UnitPrice column
2) What exactly is "ON" for? Is this a direction to the compiler, and if so, what does it mean exactly?
When you join to tables, you need to mention how those 2 tables are related and joined. The relationship is mention in ON clause.
3) Why is there both GROUP BY and ORDER BY? Is because the first is related to the JOIN, and the 2nd is related to the result set?
In your query, group by is used to group all rows with same category.
Order by is used to sort the categories.
It would be good to refer to some articles/books to learn more about Joins/Group by and Order by clauses.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply