[MySQL] GROUP BY: contains nonaggregated column

[xehbit]

Met MySQL probeer ik de volgende query uit te voeren

code:

SELECT id, name FROM table GROUP BY name;

op de onderstaande dataset, wanneer MySQL niet in strict mode staat (only_full_group_by) krijg ik de volgende fout melding:

code:

SELECT list is not in GROUP BY clause and contains nonaggregated column

Wanneer ik zonder deze strict mode draait krijg ik de resultaten gewoon terug zoals gewenst.

Wanneer ik de query aanpas naar

code:

SELECT name FROM table GROUP BY name;

krijg ik wel alle unieke waarden terug uit de name column, maar wil graag ook het eerste id erbij hebben welke wordt gevonden.

Wat doe ik fout?

Mijn dataset:

code:

id   name
---- ----
1    one
2    one
3    two
4    two
5    two
6    three
7    one
8    three
9    three

Gewenst resultaat:

code:

id   name
---- ----
1    one
3    two
6    three

[CyBeR]

De kolom "id" is niet op de een of andere manier geaggregeerd. Dat tweede ("gewenste") resultaat is ook raar, want waar zijn id's 2, 4, 5, 7, 8 en 9? Jij wilt de eerste, da's prima, maar dat weet de database niet want dat geef je niet aan. Probeer in plaats daarvan

code:

SELECT MIN(id), name FROM table GROUP BY name;

[EvilWhiteDragon]

Wat dacht je er van om je ID, ook in de GROUP BY mee te nemen? Ik weet niet waarom je een GROUP BY doet in deze context, maar een DISTINCT lijkt meer op zijn plaats.

[REDSD]

EvilWhiteDragon schreef op zaterdag 24 december 2016 @ 15:47:
Wat dacht je er van om je ID, ook in de GROUP BY mee te nemen? Ik weet niet waarom je een GROUP BY doet in deze context, maar een DISTINCT lijkt meer op zijn plaats.

Als je de GROUP BY op de ID doet, dan is elk resultaat uniek.
Als je ID uitleest weet je nooit zeker welk ID je krijgt.
Daarom is het antwoord van CyBeR goed volgensmij, met MIN() haal je het laagste cijfer uit de results die samengevoegd zijn door de GROUP BY

[xehbit]

CyBeR schreef op zaterdag 24 december 2016 @ 15:47:
De kolom "id" is niet op de een of andere manier geaggregeerd. Dat tweede ("gewenste") resultaat is ook raar, want waar zijn id's 2, 4, 5, 7, 8 en 9? Jij wilt de eerste, da's prima, maar dat weet de database niet want dat geef je niet aan. Probeer in plaats daarvan

code:

SELECT MIN(id), name FROM table GROUP BY name;

Dat werkt inderdaad op mijn startpost, ik had express niet alles er in gezet wat ik in mijn table heb staan, maar er zit ook nog een ORDER BY in. (Dacht dat dit niet relevant wat)

Update met sortering:

Dataset:

code:

id   name   sort
---- ----   ----
1    one    2
2    one    1
3    two    3
4    two    4
5    two    5
6    three  6
7    one    7
8    three  8
9    three  9

Query:

code:

SELECT id, name FROM table GROUP BY name ORDER BY sort ASC;

Expected result:

code:

id   name
---- ----
2    one
3    two
6    three

Fout:

code:

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'homestead.test.sort' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[EvilWhiteDragon]

REDSD schreef op zaterdag 24 december 2016 @ 15:50:
[...]


Als je de GROUP BY op de ID doet, dan is elk resultaat uniek.
Als je ID uitleest weet je nooit zeker welk ID je krijgt.
Daarom is het antwoord van CyBeR goed volgensmij, met MIN() haal je het laagste cijfer uit de results die samengevoegd zijn door de GROUP BY

Eigenlijk zijn beide antwoorden correct. Het is namelijk afhankelijk van wat je wil bereiken. Heb je een tabel waarin de combinatie van id en name vaker voorkomen dan wil je in bepaalde gevallen niet al die extra rijen met dezelfde data.
Wil je per naam maar 1 id, dan moet je inderdaad een aggregate functie gebruiken. In het geval van ID is MIN of MAX dan een voor de hand liggende bewerking. Tegelijkertijd moet je je afvragen of je datakwaliteit wel goed genoeg is. Waarom zijn er meerdere id's voor 1 naam? En, als er meerdere zijn, is gokken op het hoogste of laagste ID dan acceptabel?

[EnnaN]

xehbit schreef op zaterdag 24 december 2016 @ 15:58:
[...]


Dat werkt inderdaad op mijn startpost, ik had express niet alles er in gezet wat ik in mijn table heb staan, maar er zit ook nog een ORDER BY in. (Dacht dat dit niet relevant wat)

Update met sortering:

Dataset:

code:

id   name   sort
---- ----   ----
1    one    2
2    one    1
3    two    3
4    two    4
5    two    5
6    three  6
7    one    7
8    three  8
9    three  9

Query:

code:

SELECT id, name FROM table GROUP BY name ORDER BY sort ASC;

Expected result:

code:

id   name
---- ----
2    one
3    two
6    three

Fout:

code:

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'homestead.test.sort' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Hier heb je gewoon precies hetzelfde probleem: je weet niet WELKE sort je krijgt, omdat er meerdere kunnen zijn. Either moet je die column aggregeren (MIN, MAX, SUM, etc), of je moet je sort uit een subquery halen van die MIN(id), en daarna sorten.

[xehbit]

EvilWhiteDragon schreef op zaterdag 24 december 2016 @ 15:58:
Tegelijkertijd moet je je afvragen of je datakwaliteit wel goed genoeg is. Waarom zijn er meerdere id's voor 1 naam? En, als er meerdere zijn, is gokken op het hoogste of laagste ID dan acceptabel?

De tabel heeft aardig wat kolommen en heb eigenlijk alleen het relevante stukje geplaatst. Binnen deze tabel zijn sommige elementen gegroepeerd (zo bestaat er een group_name) om rijen samen in een groepje te plaatsen. (Er zijn andere manieren, maar ik krijg deze dataset zo aangeleverd).

EnnaN schreef op zaterdag 24 december 2016 @ 16:01:
[...]


Hier heb je gewoon precies hetzelfde probleem: je weet niet WELKE sort je krijgt, omdat er meerdere kunnen zijn. Either moet je die column aggregeren (MIN, MAX, SUM, etc), of je moet je sort uit een subquery halen van die MIN(id), en daarna sorten.

Ik sorteer toch op de sort (int) ascending, dan verwacht ik de tabel toch in deze volgorde? Of is dit niet wat je bedoeld?

[jasbroek]

Zover ik weet moet je alle non-aggregate termen in je SELECT clause in de GROUP BY zetten. Aangezien dat in jouw query niet is krijg je een error.
Wat vele hierboven al zeggen zul je een aggregate functie (min) op je ID moeten uitvoeren.

Als je de query uit de startpost aanpast naar SELECT MIN(id), name etc. Zou de error weg verholpen moeten zijn.
Of het gewenste resultaat er uit komt durf ik niet met zekerheid te zeggen

[Firefly III]

-

[ Voor 99% gewijzigd door Firefly III op 21-10-2019 09:49 . Reden: Leeg ivm privacy ]

[CurlyMo]

Als de sort ook uniek is dan is dit de enige (abstracte) manier die ik me kan bedenken voor in MySQL (zonder lijpe MySQL specifieke dingen zoals variabelen):

code:

select * from table where sort in (select min(sort) from table group by name)

Moet het in MySQL?

[ Voor 11% gewijzigd door CurlyMo op 24-12-2016 16:27 ]

[EnnaN]

xehbit schreef op zaterdag 24 december 2016 @ 16:05:
[...]


De tabel heeft aardig wat kolommen en heb eigenlijk alleen het relevante stukje geplaatst. Binnen deze tabel zijn sommige elementen gegroepeerd (zo bestaat er een group_name) om rijen samen in een groepje te plaatsen. (Er zijn andere manieren, maar ik krijg deze dataset zo aangeleverd).


[...]


Ik sorteer toch op de sort (int) ascending, dan verwacht ik de tabel toch in deze volgorde? Of is dit niet wat je bedoeld?

Het punt is dus dat voor elke 'rij' die je terugkrijgt, je een 'samenvatting' (aggregatie) van een aantal rijen krijgt. Deze rijen hebben allemaal dezelfde naam, dus dat gaat goed.
Deze rijen hebben allemaal een verschillend ID. Dat was het eerste punt: je moet uds aangeven WELK id je terugwil. Dat doe je bv met MIN.
Nu wil je sorteren op 'sort'. Maar die rijen hebben niet 1 sort -> alle rijen met die ene name hebben een sort, dus je moet eerst aangeven welke sort je wil, en dan kan je pas iets met dat result doen.

[CurlyMo]

EnnaN schreef op zaterdag 24 december 2016 @ 18:14:
[...]
Nu wil je sorteren op 'sort'. Maar die rijen hebben niet 1 sort -> alle rijen met die ene name hebben een sort, dus je moet eerst aangeven welke sort je wil, en dan kan je pas iets met dat result doen.

Als dat inderdaad zo is, want daar lijkt het niet op. In het voorbeeld van TS is 'sort' ook unieke waarde.

[EnnaN]

CurlyMo schreef op zaterdag 24 december 2016 @ 18:48:
[...]

Als dat inderdaad zo is, want daar lijkt het niet op. In het voorbeeld van TS is 'sort' ook unieke waarde.

Het is uniek misschien, maar dat doet er niet toe.
Als je grouped op name, dan krijg je bijvoorbeeld id 1 en 2 als 1 regel terug.
Dan moet je dus een aggregatie van alle velden doen, anders weet je niet naar welke waarde je kijkt.

Als we het b ij de eerste 2 rows houden:
Voor name is het duidelijk, die is gelijk voor al die regels, want daar group je op
Voor id hebben we bedacht in een eerdere post dat we de kleinste pakken, dus "min(id)", is 1.
Nu hebben we 1, one als resultaat.

Voor 'sort' hebben we nog steeds 2 en 1 als mogelijkheid, de waarden die bij id 1 en 2 horen. Zolang we niet zeggen op welke manier we van 2 en 1 besluiten welke we moeten hebben (optellen, kleinste, grootste, etc), kun je dus niet van een resultaat spreken , en weet je dus ook niet wat je er mee moet doen.

[CurlyMo]

EnnaN schreef op zaterdag 24 december 2016 @ 19:59:
[...]

Het is uniek misschien, maar dat doet er niet toe.
Als je grouped op name, dan krijg je bijvoorbeeld id 1 en 2 als 1 regel terug.
Dan moet je dus een aggregatie van alle velden doen, anders weet je niet naar welke waarde je kijkt.

Helemaal waar.

Als we het b ij de eerste 2 rows houden:
Voor name is het duidelijk, die is gelijk voor al die regels, want daar group je op
Voor id hebben we bedacht in een eerdere post dat we de kleinste pakken, dus "min(id)", is 1.
Nu hebben we 1, one als resultaat.

TS heeft niet gezegd dat hij min(id) wil als uitkomst, dat is door @CyBeR bedacht als mogelijke oplossing van het group by probleem dat TS had. Het is alleen geen oplossing voor het uiteindelijke resultaat dat TS wil bereiken. Er dus op door redeneren heeft daarom wat mij betreft weinig zin.

Voor 'sort' hebben we nog steeds 2 en 1 als mogelijkheid, de waarden die bij id 1 en 2 horen. Zolang we niet zeggen op welke manier we van 2 en 1 besluiten welke we moeten hebben (optellen, kleinste, grootste, etc), kun je dus niet van een resultaat spreken , en weet je dus ook niet wat je er mee moet doen.

Voort geredeneerd op al een redenering die volgens mij niet klopt.

De nieuwe situatie schets is:
1. Er is een primary key genaamd ID.
2. Er is een name waarin dubbele waardes kunnen voorkomen.
3. Er is een sort waarin ook unieke waardes staan (aanname) met wél een betekenis (waar ID dit niet heeft).

Zover ik het begrijp dient name uniek terug te komen gebaseerd op de min(sort) voor de name groep. Dat is wat ik in mijn oplossing heb aangedragen.

Als dat niet klopt, dan dient TS voor verdere verduidelijking te zorgen.

In PostgreSQL had het simpel zo gekund:

SQL:

select distinct on(name) id, name from table order by name, sort

Het fijne is dat dit ook werkt als sort geen unieke waarde is over alle rijen, maar per groep.

[ Voor 5% gewijzigd door CurlyMo op 24-12-2016 21:18 ]

[EnnaN]

CurlyMo schreef op zaterdag 24 december 2016 @ 21:05:
TS heeft niet gezegd dat hij min(id) wil als uitkomst, dat is door @CyBeR bedacht als mogelijke oplossing van het group by probleem dat TS had. Het is alleen geen oplossing voor het uiteindelijke resultaat dat TS wil bereiken. Er dus op door redeneren heeft daarom wat mij betreft weinig zin.

klopt, maar het is wel duidelijk een voorbeeld van wat er aan de hand is. Er zijn 2 waarden voor 'id' mogelijk, en er moet besloten worden welke je kiest.

In PostgreSQL had het simpel zo gekund:

SQL:

select distinct on(name) id, name from table order by name, sort

Het fijne is dat dit ook werkt als sort geen unieke waarde is over alle rijen, maar per groep.

Maar is dat niet hetzelfde als in mysql , en dat je dus een slecht defined result krijgt?
Nogmaals, mijn voorbeeld over de eerste 2 regels: Het resultaat , als je op name select, is 1 rij:

code:

id            name      sort
1 of 2       one      2 of 1

Nou voor de eerste en de derde column is het resultaat niet gedefinieerd in mysql (en ik gok ook niet in pstgress, hoewel er wel een default kan zijn). Daarom, als voorbeeld, die min. Tuurlijk wil je dat niet ,m aar het is handig om aan te geven dat je moet kiezen HOE je dat resultaat wil verkrijgen.

Als je wilt sorteren op die laastste kolom, dan moet je wel weten of het de waarde 2 of 1 betreft.

[CurlyMo]

EnnaN schreef op zaterdag 24 december 2016 @ 22:07:
Maar is dat niet hetzelfde als in mysql , en dat je dus een slecht defined result krijgt?
Nogmaals, mijn voorbeeld over de eerste 2 regels: Het resultaat , als je op name select, is 1 rij:

Nee hoor, in Postgres is dat perfect voorspelbaar Daarom doe ik zelf niks met MySQL. Een meer portable manier (behalve MySQL) is gebruik van window functies. Dit werkt zowel in Oracle als Postgres:

SQL:

select
    id,
    name
from
    (
    select
        id,
        name,
        row_number() over (partition by name order by sort) as row
    from
        table
    ) a
where
    row = 1;

[ Voor 27% gewijzigd door CurlyMo op 24-12-2016 22:24 ]

[EnnaN]

maar nu ga jij een kant op waar we het niet over hebben

Terugkomende op het probleem, mijn voorbeeld was dus wel degelijk van toepassing.

De laatste post zegt dat er ook op 'sort' gesorteerd moet worden. Dat lijkt niet alsof het intern binnen die group bedoelt is, maar achteraf ook. Als je dat wil doen moet je toch echt aangeven welke operatie je op die kolom ("2 of 1") wilt doen, voor je kan sorten. Dus ik zou zeggen dat we maar op de TS wachten tot die verteld wat we nou willen bereiken hier.

Wat ik in ieder geval probeer duidelijk te maken is WAAROM je een aggregatie op die kolom moet doen, en dat zal sowieso moeten wil je dat voorbeeld wat ik geef tot iets djuidelijks krijgen.

[CurlyMo]

EnnaN schreef op zaterdag 24 december 2016 @ 22:33:
maar nu ga jij een kant op waar we het niet over hebben

Klopt, maar om twee redenen:
1. Je zegt dat het in Postgres mogelijk ook onvoorspelbaar is. Ik wil laten zien dat dat niet zo is.
2. Het prettig zou zijn om een andere database te mogen gebruiken, omdat dat dingen dan wat makkelijker gerealiseerd kunnen worden. Veel ontwikkelaars kijken niet verder dan MySQL omdat dat nu eenmaal de bekendste database is. Het is goed om te laten zien dat dat wat mij betreft onterecht is.

Dus ik zou zeggen dat we maar op de TS wachten tot die verteld wat we nou willen bereiken hier.

Helemaal eens. In mijn voorbeelden heb ik alleen aangegeven hoe TS tot zijn "expected result" kan komen (binnen verschillende databases). Of dat voldoende is in zijn complete tabel blijft nog onduidelijk.

[ Voor 9% gewijzigd door CurlyMo op 24-12-2016 23:31 ]

[REDSD]

CurlyMo schreef op zaterdag 24 december 2016 @ 16:25:
Als de sort ook uniek is dan is dit de enige (abstracte) manier die ik me kan bedenken voor in MySQL (zonder lijpe MySQL specifieke dingen zoals variabelen):

code:

select * from table where sort in (select min(sort) from table group by name)

Moet het in MySQL?

Dit is denk ik de beste en snelste manier om die data uit mysql te halen.
Als de data te groot/traag is, dan zou je van te voren de data in de sql moeten updaten met een kolom zodat je weet welk record eerste is en kan je een simpele query gebruiken om de data op te halen.

[xehbit]

CurlyMo schreef op zaterdag 24 december 2016 @ 16:25:
Als de sort ook uniek is dan is dit de enige (abstracte) manier die ik me kan bedenken voor in MySQL (zonder lijpe MySQL specifieke dingen zoals variabelen):

code:

select * from table where sort in (select min(sort) from table group by name)

Yes, dit geeft inderdaad het juiste, verwachte resultaat met de strict mode van MySQL aan.

CurlyMo schreef op zaterdag 24 december 2016 @ 16:25:
Moet het in MySQL?

Helaas wel, de dataset is compleet in MySQL opgezet (opgeleverd).

[ACM]

CurlyMo schreef op zaterdag 24 december 2016 @ 16:25:
Als de sort ook uniek is dan is dit de enige (abstracte) manier die ik me kan bedenken voor in MySQL (zonder lijpe MySQL specifieke dingen zoals variabelen):

code:

select * from table where sort in (select min(sort) from table group by name)

Moet het in MySQL?

Als sort niet gegarandeerd uniek is over de gehele dataset, dan krijg je extra records bij deze query. Onderstaande query werkt ook als het alleen per name uniek is:

code:

select * from table where (name, sort) in (select name, min(sort) from table group by name)

Vziw ondersteund MySQL gewoon row-constructs, dus deze werkt bij mijn weten ook daarin.

Een alternatief zou zoiets zijn:

code:

select * from table t where sort = (select min(sort) from table t2 where t2.name = t.name)

Maar wederom werkt die alleen als de sort uniek is per name, in beide gevallen krijg je anders meerdere records terug per name.

[ Voor 5% gewijzigd door ACM op 29-12-2016 18:13 ]