CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-byaverage
Plazza Sele
Plazza Sele

Reputation: 319

Select average of a column

I have a table named tblPurchaseSub. In this table I have the columns Barcode that can be in several lines, ItemName, PPriceID. I want to get the Barcode, ItemName and the average price of PPriceID column. 

Barcode | ItemName  | PPriceID
----------------------------
2323      Item 1      12
2424      Item 2      14
2121      Item 3      15
2323      Item 1      11
2323      Item 1      13
2424      Item 2      15

I have tried: Select Barcode, ItemName, AVG(PPriceID) from tblPurchaseSub group by BarCode

but it seems this is not the correct way to do it.

Upvotes: 0

Views: 226

Answers (1)

GMB
GMB

Reputation: 222652

In standard SQL, every column in the SELECT clause that is not within an aggregate function (such as AVG()) must appear in the GROUP BY clause.

So to make your code a valid aggregation query, you need to add column ItemName to the group by clause:

select Barcode, ItemName, AVG(PPriceID) avgPPriceID
from tblPurchaseSub 
group by BarCode, ItemName

This gives you one row per (Barcode, ItemName) tuple, along with the average PPriceID of the corresponding rows in the original table.

Upvotes: 1

Related Questions