In my subquery result I have concatenated first_name and last_name. In my outer query I'm calling subquery "temp". Since I have concatenated columns in my subquery, how to call that concatenated column in my outer query?

For example purpose, I have put Employee name within quotes which I know is why it's giving incorrect result by showing "Employee Name" as the 1st column values instead of showing actual employee names.

SQL query:

Select "Employee Name", 
       dept_name, Salary, 
       sum(Salary) OVER (partition by dept_name) as "Total Sal Dept", 
       avg(Salary) OVER (partition by dept_name) as "Avg Sal / Dept"
FROM (   
       Select concat(employees.first_name, ", ",employees.last_name) as "Employee Name", 
              departments.dept_name, 
              Max(salaries.salary) as "Salary"
       FROM departments 
       JOIN dept_manager ON (departments.dept_no = dept_manager.dept_no) 
       JOIN salaries ON (dept_manager.emp_no = salaries.emp_no) 
       JOIN employees ON (salaries.emp_no = employees.emp_no)
       Group by employees.first_name, employees.last_name, departments.dept_name
     ) as temp

Subquery temp result

Outer query with subquery temp result

CodePudding user response：

Use back-ticks instead of double-quotes.

select `Employee Name`
from (select 'your, name' AS "Employee Name") temp

dbfiddle.uk

Better yet, avoid putting spaces in your column names while building your output. If you must have them formatted with spaces, do it at the very end.

select employee_name AS "Employee Name"
from (select 'your, name' AS employee_name) temp