How to add another column to my query that is in another table

0
SELECT  DEPARTMENT_ID
    ,(SELECT DEPARTMENT_NAME FROM HR.DEPARTMENTS) AS 'Nome do Departamento'
    ,MIN(SALARY) AS 'salario minimo'
    ,MAX(SALARY) AS 'salario maximo'
    ,CAST(AVG(SALARY) AS NUMERIC(8,2)) AS 'salario medio'
FROM [HR].[EMPLOYEES]
GROUP BY DEPARTMENT_ID

First of all I'm only one started in sql-server! I want to put the department name next to your ID, the problem is that it gives me the following error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =, & Lt ;, < =, & gt ;,> = or when the subquery is used as an expression. "

    
asked by anonymous 20.10.2018 / 19:57

1 answer

1

This error occurs by generating more than one result in the subselect because it is without any sort of filtering, suggesting a where, example

SELECT  DEPARTMENT_ID
    ,(SELECT nome_dep FROM HR.DEPARTMENTS where id_hr.departments = DEPARTMENT_ID) AS 'Nome do Departamento'
    ,MIN(SALARY) AS 'salario minimo'
    ,MAX(SALARY) AS 'salario maximo'
    ,CAST(AVG(SALARY) AS NUMERIC(8,2)) AS 'salario medio'
FROM [HR].[EMPLOYEES]
GROUP BY DEPARTMENT_ID
    
20.10.2018 / 20:06