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!
Best Answer
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
ORDER BY
WITH ROLLUP
name
You get this
Here is what should happen
id
NULLBased on the desired output, you want
Cash
,Payables
andIssued 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
I have used
WITH ROLLUP
to answer many posts in the DBA StackExchangeJun 20, 2014
: Get only overall summary WITH ROLLUP and GROUP BY for multiple fieldsAug 12, 2014
: Fetch data from same table using two group by clauses in mysqlApr 11, 2014
: Why is MySQL not using the index with the higher cardinality?Sep 21, 2014
: Monthly report by timeJul 31, 2013
: TokuDB database size unknown in phpmyadminJul 10, 2013
: How to estimate/predict data size and index size of a table in MySQLJul 03, 2013
: Information about Disk Storage MySQLApr 25, 2013
: WITH ROLLUP WHERE x IS NULLGive it a Try !!!