Comparing an aggregate function's result against a column

Question

i have 2 tables like these;

invoices

id | total_amount | currency | discount

payments

id | invoice | amount

As you can guess, payments table keeps records of the payment made to a specific invoice.

I need a query to get invoices that has been partially paid;

SELECT
    invoices.*,
    SUM(payments.amount) as paid
FROM
    invoices
LEFT JOIN
    payments ON payments.invoice = invoices.id
GROUP BY
    invoices.id

I am being able to get paid amount with this query but also i need to do something like

WHERE paid < total_amount AND paid > 0

But this says "Unknown colum 'paid' in 'where clause'". How can I get this work?

Answer 1

This is one way:

SELECT
    x.*
FROM 
(
    SELECT
        invoices.*,
        SUM(payments.amount) as paid
    FROM
        invoices
    LEFT JOIN
        payments ON payments.invoice = invoices.id
    GROUP BY
        invoices.id
) AS x
WHERE x.paid < x.total_amount 
AND   x.paid > 0;

Due to how logical query processing works, columns (like aggregates) that are expressions in the select list can't be used in portions of the query that are processed before them.

Answer 2

Aggregate results don't go in the WHERE clause, they go in the HAVING clause:

SELECT
    invoices.*, --as noted in comment, any desired fields from here must be in the GROUP BY
    SUM(payments.amount) as paid 
FROM
    invoices 
LEFT JOIN
    payments ON payments.invoice = invoices.id 
GROUP BY
    invoices.id 
HAVING SUM(payments.amount) > 0 AND SUM(payments.amount) < invoice.total_amount

EDIT: If your intention is to have all fields from Invoices and only the total paid from Payments, you might try a sub-select:

SELECT
    invoices.*, 
    p.paid 
FROM
    invoices 
LEFT JOIN
    (SELECT invoice, SUM(amount) as paid
     FROM payments 
     GROUP BY invoice
    ) p ON p.invoice = invoices.id 
WHERE p.paid > 0 AND p.paid < invoice.total_amount