Showing an ID with Average Price

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Showing an ID with Average Price

Author

Topic

BBoyd
Starting Member

4 Posts

Posted - 2012-02-15 : 18:37:50

I have a table with items that belong to 9 different categories. I need to show the CatID and the average price of all products that belong to that CatID. Below is the code that seems correct, but I get the following error.

You tired to execute a query that does not include the specified expression 'UnitPrice / VAL(QuantityPerUnit)' as part of an aggregate function.

I'm not sure what that means or where I went wrong. I have tried group by using both UnitPrice and PiecePrice, but still get an error.

Any willing contributors willing to help me?

SELECT CatID, UnitPrice / VAL(QtyPerUnit) AS PiecePrice
FROM Product
GROUP BY CatID;

singularity
Posting Yak Master

153 Posts

Posted - 2012-02-15 : 19:16:21

I'm just guessing here since I have no idea what "VAL" is supposed to be. If this is not what you're looking for, provide some sample data and the expected results.


SELECT CatID, AVG(UnitPrice / QtyPerUnit) AS PiecePrice
FROM Product
GROUP BY CatID;

BBoyd
Starting Member

4 Posts

Posted - 2012-02-15 : 19:38:56

Some sample data for what is in (QtyPerUnit).

10 boxes x 20 bags
24 - 12 oz bottles
12 - 550 ml bottles

The field is mixed and using VAL to only take the numbers at the beginning of the field.

Thanks for the help as it worked as it should. The revised code is...

SELECT CatID, AVG(UnitPrice / VAL(QuantityPerUnit)) AS PiecePrice
FROM Product
GROUP BY CatID;

Maybe I have a tired mind as I cannot believe I forgot to even include AVG.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources