FREE AI-Enhanced Online MariaDB Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE `book2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datum` datetime NOT NULL,
  `eur` decimal(12,2) NOT NULL,
  `typ` enum('b','d','w') NOT NULL DEFAULT 'b',
  PRIMARY KEY (`id`)
);

INSERT INTO `book2` (`datum`, `eur`, `typ`) VALUES
  ("2025-08-15", 10, 'd'),
  ("2025-08-16", 20, 'd'),
  ("2025-08-01", 30, 'w'),
  ("2025-08-30", 40, 'w'),
  ("2025-08-17", 50, 'w');

# falsch:
SELECT b2.*, COALESCE((SELECT SUM(book2.eur)
              FROM book2
              WHERE book2.id <= b2.id
              AND typ='w'
              ORDER BY datum ASC), 0) AS eur_sum
FROM book2 AS b2
WHERE typ!='b'
ORDER BY datum ASC;

# falsch:
SELECT b2.*, COALESCE((SELECT
              SUM(IF(book2.typ='w', book2.eur, 0))
              FROM book2
              WHERE book2.datum <= b2.datum
              AND typ != 'b'
              ORDER BY typ ASC, datum ASC), 0) AS eur_sum
FROM book2 AS b2
WHERE typ != 'b'
ORDER BY typ ASC, datum ASC;

# richtig:
SELECT   t1.*,
         SUM(IF(t1.typ='w' AND t2.typ='w', t2.eur, 0)) AS eur_sum
FROM     book2 t1
         LEFT JOIN book2 t2 ON t2.datum <= t1.datum
GROUP BY id
ORDER BY typ ASC, datum ASC;

id	datum	eur	typ	eur_sum
3	2025-08-01 00:00:00	30.00	w	30.00
1	2025-08-15 00:00:00	10.00	d	0.00
2	2025-08-16 00:00:00	20.00	d	0.00
5	2025-08-17 00:00:00	50.00	w	120.00
4	2025-08-30 00:00:00	40.00	w	70.00

id	datum	eur	typ	eur_sum
1	2025-08-15 00:00:00	10.00	d	30.00
2	2025-08-16 00:00:00	20.00	d	30.00
3	2025-08-01 00:00:00	30.00	w	30.00
5	2025-08-17 00:00:00	50.00	w	80.00
4	2025-08-30 00:00:00	40.00	w	120.00

id	datum	eur	typ	eur_sum
1	2025-08-15 00:00:00	10.00	d	0.00
2	2025-08-16 00:00:00	20.00	d	0.00
3	2025-08-01 00:00:00	30.00	w	30.00
5	2025-08-17 00:00:00	50.00	w	80.00
4	2025-08-30 00:00:00	40.00	w	120.00