Find average SQL statement multiple tables

Trying to get the average with a amount of times a user has purchases brocolli and then with the price at that time. A 0 if the user has not purchased any. This is working and saying it can’t see the Name column. What am I missing?

SELECT U.ID, U.NAME, COALESCE(AVG(P.PRICE),0) AS SELLPRICE FROM USERS AS U 
LEFT JOIN PURCHASES AS P ON U.ID=P.USERID AND P.FoodId=1 GROUP BY U.ID;

EDIT

SELECT P.USERID, U.NAME, AVG(P.PRICE) AS "Sell Price"
FROM PURCHASES AS P INNER JOIN USERS AS U ON CASE WHEN P.ID NOT NULL THEN 
"Sell Price" ELSE 0 WHERE P.FOODID=1

I also tried simplifying to just use the purchasers table and get wrong results but maybe I can tweek this as it runs.

SELECT AVG(A.Price),ID FROM PURCHASES AS A WHERE FOODID=1 GROUP BY ID;

To be honest this was in part this issue with the compiler I was using as it was browser site compiler so even when I had it working on my machine it was giving different results on the site. I ended using an inner join on the two tables. Update This ran correctly for the answer. Thank you.

SELECT U.ID, U.NAME, AVG(P.PRICE) FROM USERS AS U LEFT JOIN  PURCHASES AS P ON U.ID = P.USERID AS P AND P.FoodId=1 GROUP BY U.ID, P.NAME;

Answer

You need a LEFT JOIN of USERS to PURCHASES and GROUP BY user:

SELECT 
  U.ID, U.NAME, 
  COALESCE(AVG(P.PRICE), 0) AS "Sell Price",
  COUNT(P.USERID) AS "Number of purchases"
FROM USERS AS U LEFT JOIN PURCHASES AS P 
ON P.USERID = U.ID AND P.FOODID = 1
GROUP BY U.ID, U.NAME

Advertisement

Answer