How to get the SUM between dates in a WHERE clause? I'm trying to get the SUM between each date in the WHERE clause of the following query, not the cummulative sum, but the sum for the period in between the dates. For example:

Query:

SELECT
cdr.datefield AS 'date',
a.id AS 'id',
a.name AS 'name',
SUM(t.debit_amount - t.credit_amount) AS 'balance'
FROM calendar cdr
JOIN transactions t ON (cdr.datefield &gt;= 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 
ORDER BY a.id ASC


Records:

transactions:
123, 2012-01-01, 10000, 100, 0
124, 2012-01-01, 30000, 100, 0
125, 2013-01-01, 20000, 200, 0
126, 2014-01-01, 20000, 0, 100
127, 2014-01-01, 10000, 0, 200


Desired Result:

date        id      name                balance
-----------------------------------------------
2014-03-31  10000   Cash                -200
2014-03-31  20000   Payables            -100
2014-03-31  30000   Issued Capital      0    

2013-03-31  10000   Cash                0
2013-03-31  20000   Payables            200        
2013-03-31  30000   Issued Capital      0

2012-03-31  10000   Cash                100
2012-03-31  20000   Payables            0
2012-03-31  30000   Issued Capital      100


Tables:

calendar:
datefield (date) 
transactions:
id (int), value_date (date), account_id, debit_amount (decimal), credit_amount (decimal)
accounts:
id (int), name (varchar), description (varchar)


I've already tried the following GROUP BY statements with no luck:

GROUP BY YEAR(cdr.datefield), MONTH(cdr.datefield), a.id
GROUP BY YEAR(cdr.datefield), a.id
GROUP BY YEAR(t.value_date), MONTH(t.value_date), a.id
GROUP BY YEAR(t.value_date), a.id


I'm thinking my problem has to do with the fact that I want to group by dates that not necessarily belong to the same year.

How can I modify my query to get the desired result?

Thanks!
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 &gt;= 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 &gt;= 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 !!!