FREE AI-Enhanced Online MySQL Compiler - For learning & practice

Untitled*

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

-- INIT database
CREATE TABLE `patrimoine_instruments` (
  `instrument_id` int UNSIGNED NOT NULL,
  `instrument` varchar(50) NOT NULL,
  `instrument_ISIN` char(12) DEFAULT NULL COMMENT 'ISO 6166'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO patrimoine_instruments VALUES (1, 'TEST1', 'FR0000050353');
INSERT INTO patrimoine_instruments VALUES (2, 'TEST2', 'FR0000050354');

CREATE TABLE `patrimoine_transactions` (
  `transaction_id` int UNSIGNED NOT NULL,
  `instrument` int UNSIGNED NOT NULL,
  `quantite` decimal(11,5) NOT NULL,
  `valeur` decimal(11,5) NOT NULL,
  `datetime` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `patrimoine_transactions` (`transaction_id`,`instrument`, `quantite`, `valeur`, `datetime`) VALUES
(1, 1, 2190.00000, 10.00000, '2012-01-19 11:33:01'),
(2, 1, -90.00000, 11.00000, '2013-01-19 11:33:01'),
(3, 2, 100.00000, 10.00000, '2014-01-19 11:33:01'),
(4, 2, 10.00000, 11.00000, '2015-01-19 11:33:01');

CREATE TABLE `quote` (
  `quote` bigint UNSIGNED NOT NULL,
  `close_datetime` datetime NOT NULL,
  `quote_ISIN` char(12) COLLATE utf8mb4_general_ci NOT NULL,
  `open` decimal(10,4) DEFAULT NULL,
  `high` decimal(10,4) DEFAULT NULL,
  `low` decimal(10,4) DEFAULT NULL,
  `last` decimal(10,4) NOT NULL,
  `close` decimal(10,4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `quote` (`quote`, `close_datetime`, `quote_ISIN`, `open`, `high`, `low`, `last`, `close`) VALUES
(1, '2025-01-17 00:00:00', 'FR0000050353', 26.0000, 26.0000, 26.0000, 26.0000, 26.0000),
(2, '2025-01-16 00:00:00', 'FR0000050353', 26.0000, 26.0000, 25.0000, 26.0000, 26.0000),
(3, '2025-01-15 00:00:00', 'FR0000050354', 24.0000, 26.0000, 24.0000, 26.0000, 26.0000),
(4, '2025-01-14 00:00:00', 'FR0000050353', 24.0000, 24.0000, 24.0000, 24.0000, 24.0000),
(5, '2025-01-13 00:00:00', 'FR0000050353', 23.0000, 24.0000, 23.0000, 24.0000, 24.0000),
(6, '2025-01-10 00:00:00', 'FR0000050353', 23.0000, 24.0000, 23.0000, 23.0000, 23.0000),
(7, '2025-01-09 00:00:00', 'FR0000050353', 23.0000, 23.0000, 22.0000, 22.0000, 22.0000),
(8, '2025-01-08 00:00:00', 'FR0000050353', 22.0000, 23.0000, 22.0000, 23.0000, 23.0000),
(9, '2025-01-07 00:00:00', 'FR0000050353', 22.0000, 23.0000, 22.0000, 22.0000, 22.0000),
(10, '2025-01-06 00:00:00', 'FR0000050353', 22.0000, 23.0000, 22.0000, 22.0000, 22.0000),
(11, '2025-01-03 00:00:00', 'FR0000050353', 22.0000, 22.0000, 22.0000, 22.0000, 22.0000),
(12, '2025-01-02 00:00:00', 'FR0000050353', 22.0000, 22.0000, 22.0000, 22.0000, 22.0000),
(13, '2024-12-31 00:00:00', 'FR0000050354', 22.0000, 22.0000, 22.0000, 22.0000, 22.0000);

-- QUERY database
SELECT 
patrimoine_instruments.`instrument` as actif,
SUM(DISTINCT patrimoine_transactions.`quantite`) as quantite,
`quote`.last as valeur,
MAX(quote.close_datetime) as date
FROM `patrimoine_transactions`
LEFT JOIN patrimoine_instruments ON patrimoine_transactions.instrument = patrimoine_instruments.instrument_id
LEFT JOIN quote ON patrimoine_instruments.instrument_ISIN = quote.quote_ISIN
GROUP BY patrimoine_instruments.`instrument`
HAVING SUM(`quantite`) <> 0
ORDER BY `patrimoine_instruments`.`instrument` ASC;

ERROR 1055 (42000) at line 53: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sandbox_db.quote.last' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by