CODEWITHSUNDEEP
mysqlsql
steakman1917
steakman1917

Reputation: 3

How to fix multiple subqueries in where clause

I'm working on a query for an employee table that deals with deductions. There can be single deductions that happen once a year, or recurring deductions that happen 26 times a year (every other week). In my query, I need to add the single deduction and total value of the recurring deductions (so 26*fixed_amount). I add these up and see if they are greater or equal to a specified value. I am somewhat of a novice when it comes to SQL, so it's possible I'm going about this the wrong way.

I have a query that does subqueries and calculates total_amount. It is similar to the SQL query I will show - except the subqueries are before the WHERE clause. In that query, I get a table of all employees and their total_amount deducted. 

SELECT f.id, f.employeeid, e.lastname last_name
FROM deductions f
LEFT JOIN employees e on e.employeeid = f.employeeid
WHERE e.employeeid = f.employeeid
AND f.year = '2020'
AND EXISTS (
    (SELECT IFNULL(
        (SELECT SUM(f2.amount*26)  FROM deductions f2
        WHERE f2.payment_type = 'recur'
        AND f2.year=2020
        AND f2.employeeid=f.employeeid
        GROUP BY f2.empid) ,0)
    ) recurring,

    (SELECT IFNULL(
        (SELECT SUM(f2.amount)  FROM deductions f2
        WHERE f2.payment_type = 'one'
        AND f2.year=2020
        AND f2.employeeid=f.employeeid
        GROUP BY f2.empid) ,0)
    ) single,

    (SELECT recurring + single ) total_amount >= 100 
);

What I'd like for my returned rows are only employees that have a total_amount >= 100.

Upvotes: 0

Views: 155

Answers (1)

Barmar
Barmar

Reputation: 780798

Don't use EXISTS(). Use the subqueries as expressions in the WHERE clause directly.

It's also possible to do the two calculations in a single subquery.

SELECT f.id, f.employeeid, e.lastname last_name
FROM deductions f
LEFT JOIN employees e on e.employeeid = f.employeeid
WHERE f.year = '2020'
AND (SELECT IFNULL(SUM(IF(f2.payment_type = 'recur', f2.amount * 26, 0)), 0) +
            IFNULL(SUM(IF(f2.payment_type = 'once', f2.amount, 0)), 0)
    FROM deductions f2
    WHERE f2.year=2020
    AND f2.employeeid=f.employeeid) >= 100

Also, you don't need both ON e.employeeid = f.employeeid and WHERE e.employeeid = f.employeeid. It should only be in the ON clause.

And you don't need GROUP BY in the subquery, because it's a correlated subquery that's only processing that employee ID.

Upvotes: 2

Related Questions