MySQL: JOIN sum of multiple values

I have a query that calculates an invoice from table invoice. This invoice has taxes associated to it located in tax_recv table. The tax_recv will have multiple rows that are tied to an invoice in invoice table.

I have a query that calculates 12 months worth of invoices and orders them by their corresponding date. Here is the query:

SELECT 
    invoice_amount + late_fee + SUM(c.tax) AS amount, tollfree_json, date_generated 
FROM 
    invoices as i 
LEFT JOIN 
    csi_tax_recv as c 
ON 
    c.invoice_number = i.id 
WHERE 
    DATE_FORMAT(date_generated,'%Y-%m') < DATE_FORMAT(NOW(),'%Y-%m') 
AND 
    DATE_FORMAT(date_generated,'%Y-%m') >= DATE_FORMAT(NOW() - INTERVAL 12 MONTH,'%Y-%m') 
ORDER BY 
    date_generated

The only problem with this query, is it is only returning one row? Not sure exactly why. The minute I remove the left join and the SUM(c.tax) (which is what I think is causing the issue), the query works great.

The end result should look like this:

invoice_amount + total_taxes_for_invoices,  tollfree_json, date_generated

Cheers.

Answers


As people said, you need to group by the fields you want to get the sum of the taxes and make calculations with that sum, something like this:

   SELECT
        i.tollfree_json,
        i.date_generated,
        (i.invoice_amount + i.late_fee + SUM(c.tax)) AS amount

    FROM
        invoices as i JOIN csi_tax_recv as c ON i.id = c.invoice_number

    WHERE
        DATE_FORMAT(date_generated,'%Y-%m') < DATE_FORMAT(NOW(),'%Y-%m')
    AND
        DATE_FORMAT(date_generated,'%Y-%m') >= DATE_FORMAT(NOW() - INTERVAL 12 MONTH,'%Y-%m')

    GROUP BY
        i.tollfree_json,
        i.date_generated

    ORDER BY
        i.date_generated

With this query, you will get the sum of the taxes aggregated by every tollfree_json and date_generated combination, and you can add the invoice_amount and late_fee to that sum, if this is what you looked for.


SELECT 
    invoice_amount + late_fee + SUM(c.tax) AS amount, tollfree_json, date_generated 
FROM 
    invoices as i 
LEFT JOIN 
    csi_tax_recv as c 
ON 
    c.invoice_number = i.id 
WHERE 
    DATE_FORMAT(date_generated,'%Y-%m') < DATE_FORMAT(NOW(),'%Y-%m') 
AND 
    DATE_FORMAT(date_generated,'%Y-%m') >= DATE_FORMAT(NOW() - INTERVAL 12 MONTH,'%Y-%m') 
GROUP BY  date_generated
    ORDER BY 
        date_generated

Looking over your query, I would normally say group on everything before the aggregate, but in this case, I'm not sure it makes sense. So group by what you order by (date_generated_) will group them on the associated date of invoice.


As Vyskol mentioned in his comment I'm also sure you need to use a group by clause with all fields not used in an aggregate function.

Try this, hope this works:

SELECT
    i.tollfree_json,
    i.date_generated,
    SUM(i.invoice_amount + i.late_fee + c.tax) AS amount

FROM
    invoices as i JOIN csi_tax_recv as c ON i.id = c.invoice_number

WHERE
    DATE_FORMAT(date_generated,'%Y-%m') < DATE_FORMAT(NOW(),'%Y-%m')
AND
    DATE_FORMAT(date_generated,'%Y-%m') >= DATE_FORMAT(NOW() - INTERVAL 12 MONTH,'%Y-%m')

GROUP BY
    i.tollfree_json,
    i.date_generated

ORDER BY
    i.date_generated

Need Your Help

Node exits before async function completes

node.js async-await babeljs ecmascript-7

I have a function that returns a promise, and I am trying to await on it from within an async function. The problem is that the program completes immediately, instead of awaiting the promise.

OpenMP nested for loops (work with Qt)

c++ qt parallel-processing openmp qpainter

I am wiriting a program (C++) to merge images (QPainter). In sequentially way it's work great, but I must parallelize it with OpenMP. How can I do this? I think that possible problem is memory allo...