CODEWITHSUNDEEP
mysqlinnodbsubquery
grapefrukt
grapefrukt

Reputation: 27045

Using results from parent queries in nested select

I'm sure this is a fairly trivial problem, but I'm not sure what to google to find the solution.

I have a table that looks like this:

CREATE TABLE IF NOT EXISTS `transactions` (
  `name` text collate utf8_swedish_ci NOT NULL,
  `value` decimal(65,2) NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci ROW_FORMAT=COMPACT;

I populate this by cutting and pasting data from my internet banking service. Value can be a negative or positive value, what both date and name contain should be fairly obvious ;) I have constructed a query to let me see my bottom line for each month: 

SELECT sum(`value`) as 'change', DATE_FORMAT(`date`, '%M %Y') as 'month'
FROM `transactions` 
WHERE 1
GROUP BY year(`date`), month(`date`)

Now I would like to add the total accumulated money in the account at the end of the month as an additional column. 

SELECT sum(`value`) as 'change', DATE_FORMAT(`date`, '%M %Y') as 'month', 
(SELECT sum(`value`) FROM `transactions` WHERE `date` <= 123) as 'accumulated'
FROM `transactions` 
WHERE 1
GROUP BY year(`date`), month(`date`)

123 is not exactly what I want in there, but I do not understand how to get at the result from my DATE_FORMAT inside that subquery.

Is this even the proper way to approach the problem?

This is mostly a personal exercise (running on a very small dataset) so I'm not very concerned about performance, readable SQL is far more important.

I am running a InnoDB table on MySQL 5.0.45

Upvotes: 1

Views: 1133

Answers (1)

Quassnoi
Quassnoi

Reputation: 425623

SELECT  change,
        CONCAT(mymonth, ' ', myyear) AS 'month',
        (
        SELECT  SUM(`value`)
        FROM    `transactions`
        WHERE   `date` < DATE_ADD(STR_TO_DATE(CONCAT('01.', mymonth, '.', myyear, '%D.%M.%Y'), INTERVAL 1 MONTH))
        )
FROM    (
        SELECT  sum(`value`) as 'change', YEAR(date) AS myyear, MONTH(date) AS mymonth
        FROM    `transactions` 
        WHERE 1
        GROUP BY 
                YEAR(`date`), MONTH(`date`)
        ) q

You wrote that you don't cate for performance, but this syntax is not much more complex but will be more efficient (just in case):

SELECT  SUM(value) AS change,
        CONCAT(MONTH(`date`), ' ', YEAR(`date`)) AS 'month',
        @r : = @r + SUM(value) AS cumulative
FROM    (
        SELECT  @r := 0
        ) AS vars,
        transactions
WHERE 1
GROUP BY 
        YEAR(`date`), MONTH(`date`)
ORDER BY
        YEAR(`date`), MONTH(`date`)

This one will count cumulative SUM's as well, but it will count each month only once.

Upvotes: 2

Related Questions