I am running a query on an employees database. I want to return rows where employees wages exceed amount $x
SELECT
employees_table.employee_id,
employees_table.employee_name,
(SELECT SUM(wages_table.wages)
FROM wages_table
WHERE wages_table.employee_id = employees_table.employee_id) AS wages
WHERE wages >= 100"
It's returning "unknown column wages"
I think you want:
SELECT employees_table.employee_id,
employees_table.employee_name,
SUM(wages_table.wages) AS wages
FROM employees_tablee
INNER JOIN wages_table ON wages_table.employee_id = employees_table.employee_id
GROUP BY 1, 2
HAVING SUM(wages_table.wages) >= 100
You can't use an alias wages >= 100 in WHERE clause.
I believe you are simply trying to make a JOIN and GROUP BY query. You can then filter by wages using HAVING for which an alias is allowed:
SELECT
e.employee_id,
e.employee_name,
SUM(w.wages) AS wages
FROM wages_table w
JOIN employee_table e ON w.employee_id = e.employee_id
GROUP BY e.employee_id
HAVING wages >= 100
try this
select * from (
SELECT employees_table.employee_id, employees_table.employee_name,
(SELECT SUM(wages_table.wages)
FROM wages_table
WHERE wages_table.employee_id = employees_table.employee_id) AS wages
)tmp where wages>=100
The steps to process a query are these
1) From statement
2) Where
3) Select
4) Order By
As you can see, the Where is executed before the Select. Therefore: all aliases defined in the select are not yet available in your Where statement. A workaround is using an inner select in your From clause that gives you the the employee_id and the sum of its wages. Then you can join them later on and your alias is usable.
Try rewrite to:
select employees_table.employee_id, employees_table.employee_name, SUM(wages_table.wages)
from employees_table,
wages_table
where wages_table.employee_id = employees_table.employee_id
group by employees_table.employee_id, employees_table.employee_name
having SUM(wages_table.wages) >= 100
You need to explain relationship between wages_table and employees_table.
If one to one relationship :
SELECT employees_table.employee_id, employees_table.employee_name, wages_table.wages
FROM wages_table, employees_table
WHERE wages_table.employee_id = employees_table.employee_id
AND wages >= 100
If one to many relationship, GROUP BY and SUM(wages_table.wages) required as other people said.
