CODEWITHSUNDEEP
sqlsql-server
simenh
simenh

Reputation: 58

Select all rows, and the average price of all rows where customerid = x

I have a table that looks like this:

[invoice_lines]

id(int), customerid(int), price(int), productname(text)

I want to query this table, and extract all rows. But I also want to extract the average price of all rows with customerid = customerid.

So lets say I have one row that has these values:

id(1), customerid(134), price(125), productname(Internet)

I want to extract that row, but I also want a column which contains the average price of all rows which has customerid 134.

Any pointers? :)

Upvotes: 0

Views: 86

Answers (2)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

DECLARE   @id INT
SET       @id = 1

SELECT    id, 
          customerid, 
          price, 
          productname,
          (
              SELECT   AVG(price) 
              FROM     invoice_lines i 
              WHERE    i.customerid = o.customerid
          )AS avgprice
FROM invoice_lines o
WHERE o.id = @id

Fiddle Demo

Upvotes: 1

qxg
qxg

Reputation: 7036

SELECT id, customerid, price, productname, 
    AVG(price) OVER (PARTITION BY customerid) AS avg_price
from invoice_lines

Upvotes: 2

Related Questions