Home / Questions / 276939
Accepted
Fabian
Asked: 2020-07-06 02:05:34 +0800 CST 2020-07-06 02:05:34 +0800 CST

I need help with a query in MYSQL

  • 772

I have these tables:

CREATE TABLE IF NOT EXISTS sucursal(
    numSucursal int PRIMARY KEY NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    direccion VARCHAR(100) NOT NULL
);

and this:

CREATE TABLE IF NOT EXISTS empleado(
    numEmpleado int PRIMARY KEY NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    sueldo bigint NOT NULL,
    fContratacion DATE NOT NULL,
    numSucursal int,
    jefe int NULL,
    CONSTRAINT fkjefe
    FOREIGN KEY (jefe) REFERENCES empleado(numEmpleado),
    CONSTRAINT fksucursal
    FOREIGN KEY (numSucursal) REFERENCES sucursal(numSucursal)
);

I have to show the average salary of each branch, but I can't find a way to do it, I know how to get the average, but not how to link the employees to a branch exactly.

When creating an employee, I assign the branch number to the field numSucursalas fk , but the rest does not work for me.

Please help, thank you very much.

mysql

1 Answers

  1. Best Answer

    I think this may work for you, try it and tell me that I've done it upside down and I haven't tried it

    SELECT s.numSucursal, 
        AVG(e.sueldo)
FROM sucursal s
INNER JOIN empleado e ON s.numSucursal=e.numSucursal
GROUP BY s.numSucursal;
    • 4