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.

The basic working of a `group by` *with* a join is no different than `group by` without a join. Btw: which DBMS are you using? Postgres? Oracle? — , Nov 18 '13 at 21:17

score 1 · Accepted Answer · answered Nov 18 '13 at 21:19

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.

answered Nov 18 '13 at 21:19

PlantTheIdea

16,061
5
35
40

Thank you for your response :) I tried that, and it runs - but something seems off. Maybe it is just with the sample database we are using. Naturally, the AVG Retail price would be higher than wholesale to make profit, but the results were very weird and not really making sense. But, it might just be the database. – user2891712 Nov 18 '13 at 21:21
I will when it allows me too. Maybe you could make sense of this though - The results are showing for example - An AVG Wholesale price of $28, with an AVG retail of $1800. Then some say, for example, AVG wholesale price of $680 with an AVG Retail of $50. It doesn't make any senese to me, but the code looks right? – user2891712 Nov 18 '13 at 21:27
without seeing the data in the tables i couldn't really tell you, but it sounds like a data integrity issue. i would do some QA to investigate the items where wholesale price is lower than retail, then look at the datapoints related to those items. – PlantTheIdea Nov 18 '13 at 21:32

Using aggregate functions with Inner Joins

1 Answers1