Mysql – How to get the sum with more then one table

I believe you can avoid joining the same table 3 times by

SELECT
    i.invoice_id,
    SUM(CASE 
        WHEN a.type_id IN (1,2,3) THEN a.amount
        ELSE 0 END) AS sum1,
    SUM(CASE 
        WHEN a.type_id IN (4,5,6) THEN a.amount
        ELSE 0 END) AS sum2,
    SUM(CASE 
        WHEN a.type_id IN (7,8,9) THEN a.amount
        ELSE 0 END) AS sum3
FROM
    invoices AS i
LEFT JOIN
    amounts AS a ON a.invoice_id = i.invoice_id 
                AND a.type_id IN (1,2,3,4,5,6,7,8,9)
GROUP BY
    i.invoice_id ;

Note: depends on your requirements, LEFT JOIN can be replaced with INNER (then you won't have invoices without amounts in resultset)

You might laugh when you here this, by there is an aggregate clause that triggers summary between breaks in values. The aggregrate clause is WITH ROLLUP. (Look in MySQL Documentation under GROUP BY Modifiers)

let's take your query and make the following changes

• Remove ORDER BY
• Substitute WITH ROLLUP
• Remove name

You get this

SELECT
cdr.datefield AS 'date',
a.id AS 'id',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
WITH ROLLUP;

Here is what should happen

• In between each datefield, there will be
  • datefield
  • id NULL
  • a sum of balance for that date
• The last row return
  • datefield NULL
  • id NULL
  • a sum of balance for all dates

Based on the desired output, you want Cash, Payables and Issued Capital as zero in case there is no transaction type for the given date. You also done want the last row because you don't want the grand total. What you do is take the new query, make it a subquery, and LEFT JOIN the accounts table to the subquery, returning zero for balance if it is NULL and exclude any row whose date is NULL.

Here is the new query

SELECT
    BB.date,AA.id,AA.name,IFNULL(BB.balance,0) balance
FROM accounts AA LEFT JOIN
(SELECT
cdr.datefield AS 'date',
a.id AS 'id',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield >= t.value_date)
JOIN accounts a ON (a.id = t.account_id)
WHERE cdr.datefield IN ('2014-03-31', '2013-03-31', '2012-03-31')
GROUP BY cdr.datefield, a.id
WITH ROLLUP) BB USING (id) WHERE AA.date IS NOT NULL
ORDER BY BB.date DESC,AA.id;

I have used WITH ROLLUP to answer many posts in the DBA StackExchange

• Jun 20, 2014 : Get only overall summary WITH ROLLUP and GROUP BY for multiple fields
• Aug 12, 2014 : Fetch data from same table using two group by clauses in mysql
• Apr 11, 2014 : Why is MySQL not using the index with the higher cardinality?
• Sep 21, 2014 : Monthly report by time
• Jul 31, 2013 : TokuDB database size unknown in phpmyadmin
• Jul 10, 2013 : How to estimate/predict data size and index size of a table in MySQL
• Jul 03, 2013 : Information about Disk Storage MySQL
• Apr 25, 2013 : WITH ROLLUP WHERE x IS NULL

Give it a Try !!!