Using aggregate functions with Inner Joins

Question

I'm having an issue understanding the Group By when using aggregate functions. I understood it fine without using inner joins, but now I'm not understanding what to group by.

Here is my code.

SELECT  ProductName,
        Products.ProductNumber,
        AVG(WholesalePrice),
        AVG(RetailPrice)


FROM    Products INNER JOIN ProductVendors
        ON Products.ProductNumber = ProductVendors.DaysToDeliver;

As you can tell, I'm trying to find the Average prices, and I don't know what to group by. I tried grouping by everything there and none will work. Any suggestions?

Here is the error: Column 'Products.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

Basically, for any DBMS you need to GROUP BY the items that you are not performing aggregate functions on:

SELECT Products.ProductName AS ProductName
    ,Products.ProductNumber AS ProductNumber
    ,AVG(Products.WholesalePrice) AS AvgWholesalePrice
    ,AVG(Products.RetailPrice) AS AvgRetailPrice
FROM Products Products
INNER JOIN ProductVendors Vendors ON Products.ProductNumber = Vendors.DaysToDeliver    
GROUP BY Products.ProductName, Products.ProductNumber;

Also, when doing JOINs you should really alias the tables appropriately and then reference the aliases with their fields. It is more transparent, requires less implicit translation by the SQL optimizer, and allows for better maintenance.