mysql SELECT issue with HAVING and/or WHERE

I have 3 tables:

patrimoine_instruments(instrument_id, instrument, instrument_ISIN)
patrimoine_transactions(transaction_id, instrument, quantite, valeur)
quote(quote, quote_ISIN, close_datetime, last)

I am trying to get the portforlio as of today:

listing all the instruments for which a quantite is not 0
with their respective quantite as of today for each instrument
with their respective most recent value from table quote for each instrument
with the most recent date from the table quote for each instrument

The below SELECT query works fine in case there are data in table quote for 1 instrument only:

SELECT 
patrimoine_instruments.`instrument` as actif, 
SUM(DISTINCT patrimoine_transactions.`quantite`) as quantite, 
(SELECT quote.last HAVING close_datetime = MAX(close_datetime) AND patrimoine_instruments.instrument_ISIN = quote.quote_ISIN) 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;

But in case there are data in table quote for more than 1 instrument, the returned data are incorrect, when returned. There must be something missing somewhere (WHERE or HAVING for example). Does anybody have an idea?

Here is a replication of the database and query. But please note that I am getting an error message that I am not getting using my website. Interestingly, it relates to the column that is returning wrong values. https://sqlfiddle.com/mysql/online-compiler?id=64d7de9a-7356-4957-86ee-9670de07f564

Solution

probably you have sql_mode=only_full_group_by enabled (which is the default), every column in a GROUP BY query needs to either be part of the GROUP BY clause or use an aggregate function. In your query, quote.last isn’t grouped. To fix this, you can use an aggregate function like MAX(quote.last) like this:

SELECT 
    patrimoine_instruments.`instrument` AS actif,
    SUM(patrimoine_transactions.`quantite`) AS quantite,
    MAX(`quote`.last) AS valeur, --MAX for aggregation
    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;

i executed it on your sqlfiddle and it works; let me know

update

I fixed the query to match the quantity and it work pretty well on that now:

SELECT 
    patrimoine_instruments.`instrument` AS actif,
    SUM(patrimoine_transactions.`quantite`) AS quantite,
    MAX(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
WHERE 
    quote.close_datetime = (SELECT MAX(close_datetime) FROM quote WHERE quote_ISIN = patrimoine_instruments.instrument_ISIN)
GROUP BY 
    patrimoine_instruments.`instrument`
HAVING 
    SUM(`quantite`) <> 0

BUT trying to improve it to also get the valeur work properly I get this error: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.quote.last' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

i suggets to switch to sql_mode in my.ini as sql_mode=NO_ENGINE_SUBSTITUTION and try it

Update 2

the query to resolve your issue is something like this:

SELECT 
    pi.`instrument` AS actif, 
    SUM(pt.`quantite`) AS quantite, 
    (
        SELECT q.`last`
        FROM quote q
        WHERE q.`quote_ISIN` = pi.`instrument_ISIN`
        ORDER BY q.`close_datetime` DESC
        LIMIT 1
    ) AS valeur,
    (
        SELECT q.`close_datetime`
        FROM quote q
        WHERE q.`quote_ISIN` = pi.`instrument_ISIN`
        ORDER BY q.`close_datetime` DESC
        LIMIT 1
    ) AS date
FROM 
    patrimoine_transactions pt
LEFT JOIN 
    patrimoine_instruments pi ON pt.`instrument` = pi.`instrument_id`
GROUP BY 
    pi.`instrument`
HAVING 
    quantite <> 0
ORDER BY 
    pi.`instrument` ASC;

As i said before it will not work and will return the error: ERROR 1055 (42000) at line 53: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'subquery.valeur' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by