greatest n per group - mysql SELECT issue with HAVING andor WHERE - Stack Overflow

admin2025-04-20 6

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.

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;

Share Improve this question edited Feb 4 at 19:43 Rick James 143k15 gold badges141 silver badges248 bronze badges asked Jan 25 at 20:08 NALL 56 bronze badges

MAX(last) sounds more like a groupwise-mac problem. – Rick James Commented Feb 4 at 19:43

Add a comment |

1 Answer 1

Sorted by: Reset to default 0

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

转载请注明原文地址:http://anycun.com/QandA/1745090771a90371.html

greatest n per group - mysql SELECT issue with HAVING andor WHERE - Stack Overflow

1 Answer 1

update

Update 2

greatest n per groupmysql SELECT issue with HAVING andor WHEREStack Overflow