sql - Find average ID then fetch data with same ID from another table

Question

I need to find the average grid_id in the client table then fetch x and y from the grid table with the corresponding id. I have tried this query here:

SELECT round(AVG(client_id)) as testyy, grid.x,grid.y 
FROM client
JOIN grid
ON client.grid_id = grid.grid_id
WHERE grid.grid_id = AVG(client_id)
group by GRID.X,GRID.Y

but cant seem to get it to work.

DESIRED OUTPUT: average grid location

EXAMPLE DATA: grid table

INSERT INTO grid (x,y) values (-10,-10);
INSERT INTO grid (x,y) values (-10,-9);
INSERT INTO grid (x,y) values (-10,-8);
INSERT INTO grid (x,y) values (-10,-7);
INSERT INTO grid (x,y) values (-10,-6);
INSERT INTO grid (x,y) values (-10,-5);
INSERT INTO grid (x,y) values (-10,-4);

EXAMPLE DATA: CLIENT TABLE

INSERT INTO client (name,payment_method,grid_id,service_id) VALUES ('Derri','cash',563,57);
INSERT INTO client (name,payment_method,grid_id,service_id) VALUES ('Eil','card',616,758);
INSERT INTO client (name,payment_method,grid_id,service_id) VALUES ('Sid','cash',595,59);
INSERT INTO client (name,payment_method,grid_id,service_id) VALUES ('Amelia','card',601,60);
INSERT INTO client (name,payment_method,grid_id,service_id) VALUES ('Lewis','card',479,61);
INSERT INTO client (name,payment_method,grid_id,service_id) VALUES ('Mel','card',480,62);
INSERT INTO client (name,payment_method,grid_id,service_id) VALUES ('Gordon','card',480,63);
INSERT INTO client (name,payment_method,grid_id,service_id) VALUES ('Gino','card',479,64);

question from:https://stackoverflow.com/questions/65849112/find-average-id-then-fetch-data-with-same-id-from-another-table

Answer 1

Find average ID then fetch data with same ID from another table

Maybe a subquery will do the trick. Try:

select x, y
from grid
where id = ( 
  select round( avg( grid_id ) )
  from client 
) ;