How to add another column to my query found in another table

Question

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 just a sql-server starter! I want to put the department name next to your ID, the problem is it gives me the following error: "Subquery returned more than 1 value. This is not permitted when the subquery Follows =, != , <, <= , >, >= or when the subquery is used as an Expression. "

Answer 1

This error occurs by generating more than one result in the subselect because it is without any type of filtering, I suggest for 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