1

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 >= 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!

2

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

Give it a Try !!!

  • Thanks Rolando! Your thorough answer is very helpful, but I think I didn't made myself clear enough. What I need is the balance for: 1) t < 2012-03-31 2) 2013-03-31 < t < 2012-03-31 3) 2014-03-31 < t < 2013-03-31 instead of 1) t < 2012-03-31 2) t < 2013-03-31 3) t < 2014-03-31. – JuanLM Nov 21 '14 at 20:50
  • I added ORDER BY BB.date DESC ,AA.id to make dates go backwards and make id go forwards within dates. – RolandoMySQLDBA Nov 21 '14 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.