Brecht Schepens Brecht Schepens - 4 months ago 7
SQL Question

MYSQL SUM of columns in joined table

I'm trying to do the following query:

SELECT
(
SELECT
COUNT(*)
FROM
pl_invoices
JOIN
pl_invoice_articles
ON
pl_invoices.invoice_id = pl_invoice_articles.invoice_article_invoice_id
WHERE
pl_invoices.invoice_amount_paid = SUM(pl_invoice_articles.invoice_article_price)
) as 'Aantal Betaald'


So: I need to know how many invoices are paid, but the only way to know that is to make the sum of all the invoice articles and compare that to the total amount paid.

This query does not work and I don't know what i'm doing wrong. Is there anyone who can help me?

Many thanks in advance!

Answer

Use having clause instead of where

SELECT cnt as 'Aantal Betaald' from (
    SELECT COUNT(*) AS CNT, invoice_amount_paid  FROM  pl_invoices pl JOIN pl_invoice_articles pla
    ON     pl.invoice_id = pla.invoice_article_invoice_id
    having     pl.invoice_amount_paid = SUM(pla.invoice_article_price)
) tt  ;
Comments