CODEWITHSUNDEEP
sqloracle-databaseplsql
23k
23k

Reputation: 1399

Querying rows that have data greater than the sum of two columns

I'm pretty new to SQL/PLSQL and having so trouble thinking of how to do this specific query.

I want to select the number of employees from the table where their total pay (the sum of salary and commission) is greater than 2900.

I understand that you can't put aggregate functions inside of a WHERE clause directly. I tried this below, and while it doesn't throw errors, is also does not give the correct results.

In SQL, how can I select the number of records where sum(x + y) > z?

DECLARE
    employee_total_pay_greater          NUMBER;
BEGIN

    SELECT COUNT(*)
    INTO employee_total_pay_greater
    FROM EMPLOYEE
    WHERE (SELECT SUM(SALARY + NVL(COMMISSION, 0))
            FROM EMPLOYEE) > 2900;

    DBMS_OUTPUT.PUT_LINE(employee_total_pay_greater);

END;

Upvotes: 1

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You don't need an aggregation function if the values are all in one row:

SELECT COUNT(*)
INTO employee_total_pay_greater
FROM EMPLOYEE
WHERE (SALARY + COALESCE(COMMISSION, 0))) > 2900;

Upvotes: 1

Related Questions