[MySql] GROUP BY query met vreemde resultaten *

[BlackShadow]

Ik zou graag de 6 leden met het meeste aantal foto's bekomen.
Na wat opzoekwerk ben ik tot de volgende query gekomen:

code:

"SELECT
WWW_members.nickname,WWW_pics.width,WWW_pics.height,WWW_pics.filename,count(WWW_pics.id) as c
FROM WWW_members
LEFT JOIN WWW_pics
ON WWW_members.profielfoto = WWW_pics.id
GROUP BY WWW_members.nickname
ORDER BY c DESC
LIMIT 6

Ik heb enkele topics hier bekeken die de functie count() behandelden en GROUP BY.
Met deze informatie heb ik bovenstaande constructie gemaakt

Graag een tip dus want ik kom er niet uit...

[ Voor 2% gewijzigd door BlackShadow op 21-01-2005 15:23 . Reden: Stuk vergeten in titel :o soz ]

[NMe]

En wat is nu je vraag?

[Verwijderd]

Ik denk dat je wat teveel data met de query ophaalt maar wat gaat er nu niet goed?

code:

"SELECT count(WWW_pics.id) as c
FROM WWW_members
GROUP BY WWW_members.nickname
ORDER BY c DESC
LIMIT 0,6

^^^ denk dat dit wel moet werken
^^^ gaat alleen niet op bij 2 tabellen

[ Voor 66% gewijzigd door Verwijderd op 21-01-2005 15:31 ]

[BlackShadow]

-NMe- schreef op vrijdag 21 januari 2005 @ 15:24:
En wat is nu je vraag?

blijkbaar geeft deze qry niet het gewenste resultaat en ik weet niet waar de fout zit...

Verwijderd schreef op vrijdag 21 januari 2005 @ 15:24:
Ik denk dat je wat teveel data met de query ophaalt maar wat gaat er nu niet goed?

Ik krijg resulaten die totaal niet op het aantal foto's gesorteerd zijn

[ Voor 38% gewijzigd door BlackShadow op 21-01-2005 15:26 ]

[mrFoce]

weeet niet precies wat je bedoelt maar volgens mij ben je een beetje onwijs fout bezig..

BV select * from TABLE order by FOTOSGEUPLOAD DESC limit 0,6

[Creepy]

BlackShadow schreef op vrijdag 21 januari 2005 @ 15:25:
[...]

blijkbaar geeft deze qry niet het gewenste resultaat en ik weet niet waar de fout zit...


[...]


Ik krijg resulaten die totaal niet op het aantal foto's gesorteerd zijn

Je sorteert ook op omschrijving en niet op aantal?

Daarnaast is je vast wel opgevallen dat in andere topics meldingen zijn gegeven over group by en MySQL. Normaal gesproken moet je elk non aggregated field (dus waar geen berekening ala count/sum e.d. staat) opnemen in je group by. In MySQL hoeft dit niet (maar het is wel verstandig om het toch te doen .)

Overigens, zou je P&W FAQ - De "quickstart" eens door willen lezen? Dan heb je de volgende keer een iets completere topicstart en een correcte titel

[ Voor 25% gewijzigd door Creepy op 21-01-2005 15:30 ]

[BlackShadow]

gideon82 schreef op vrijdag 21 januari 2005 @ 15:26:
weeet niet precies wat je bedoelt maar volgens mij ben je een beetje onwijs fout bezig..

BV select * from TABLE order by FOTOSGEUPLOAD DESC limit 0,6

Het gaat om 2 tabellen, en niet een variabele waar ik het aantal foto's bijhou.

Ik wil dus de membernaam en profielfoto ophalen en deze gesorteerd weergeven volgens heet aantal foto's die ze al hebben geuploaded

[mrFoce]

dan moet je hem JOINEN.. Zoek ff google naar JOIN MYSQL en je kan weer vooruit..

[Creepy]

gideon82 schreef op vrijdag 21 januari 2005 @ 15:30:
dan moet je hem JOINEN.. Zoek ff google naar JOIN MYSQL en je kan weer vooruit..

Hij joint ook. Zie z'n topic start.

[NMe]

Je GROUP BY klopt niet. Je moet in fatsoenlijk SQL wanneer je groupeert, grouperen op alle velden die geen aggregate functie zijn. In jouw geval wordt je GROUP BY dus:

code:

GROUP BY WWW_members.nickname,WWW_pics.width,WWW_pics.height,WWW_pics.filename

Bah, jij vieze Creepy, snel je post editen zodat mijn post overbodig wordt he?

[ Voor 24% gewijzigd door NMe op 21-01-2005 15:33 ]

[BlackShadow]

Creepy schreef op vrijdag 21 januari 2005 @ 15:28:
[...]

Je sorteert ook op omschrijving en niet op aantal?

Overigens, zou je P&W FAQ - De "quickstart" eens door willen lezen? Dan heb je de volgende keer een iets completere topicstart en een correcte titel

count(WWW_pics.id) is toch een aantal
dan geef ik de alias c
en sorteer op c

Sorry voor de topictitel, mijn excuses daarvoor.

[Creepy]

* Creepy keek ff over de c heen. BLijft de group by nog staan

[mouse256]

Voor zover ik het snap moet je het andersom doen:

code:

SELECT
WWW_members.nickname,WWW_pics.width,WWW_pics.height,WWW_pics.filename,count(WWW_pics.id) as c
FROM WWW_pics
LEFT JOIN WWW_members
ON WWW_members.profielfoto = WWW_pics.id
GROUP BY WWW_pics.id
ORDER BY c DESC
LIMIT 6

Je wil namelijk uit je picstabel de pics die bij dezelfde member horen bijeen tellen, dus je moet die groeperen, www_members.nickname gaat volgens mij vrij uniek zijn :-)

[curry684]

P&W FAQ - SQL - Hoe werkt GROUP BY nu in godesnaam?.

[Creepy]

-NMe- schreef op vrijdag 21 januari 2005 @ 15:31:


Bah, jij vieze Creepy, snel je post editen zodat mijn post overbodig wordt he?

offtopic:
Het kan nog altijd erger zoals een Curry die een Titelfix doet net nadat ik dat ook had gedaan!

[curry684]

Creepy schreef op vrijdag 21 januari 2005 @ 15:43:
[...]

offtopic:
Het kan nog altijd erger zoals een Curry die een Titelfix doet net nadat ik dat ook had gedaan!

offtopic:
aai

[BlackShadow]

-NMe- schreef op vrijdag 21 januari 2005 @ 15:31:
Je GROUP BY klopt niet. Je moet in fatsoenlijk SQL wanneer je groupeert, grouperen op alle velden die geen aggregate functie zijn. In jouw geval wordt je GROUP BY dus:

code:

GROUP BY WWW_members.nickname,WWW_pics.width,WWW_pics.height,WWW_pics.filename

Bah, jij vieze Creepy, snel je post editen zodat mijn post overbodig wordt he?

[afbeelding]

code:

SELECT
WWW_members.nickname,WWW_pics.width,WWW_pics.height,WWW_pics.filename,count(WWW_pics.id) as c
FROM WWW_members
LEFT JOIN WWW_pics
ON WWW_members.profielfoto = WWW_pics.id
GROUP BY WWW_members.nickname,WWW_pics.width,WWW_pics.height,WWW_pics.filename
ORDER BY c DESC
LIMIT 6

Geeft nog steeds geen goede resultaten...

mouse256 schreef op vrijdag 21 januari 2005 @ 15:33:
Voor zover ik het snap moet je het andersom doen:

code:

SELECT
WWW_members.nickname,WWW_pics.width,WWW_pics.height,WWW_pics.filename,count(WWW_pics.id) as c
FROM WWW_pics
LEFT JOIN WWW_members
ON WWW_members.profielfoto = WWW_pics.id
GROUP BY WWW_pics.id
ORDER BY c DESC
LIMIT 6

Je wil namelijk uit je picstabel de pics die bij dezelfde member horen bijeen tellen, dus je moet die groeperen, www_members.nickname gaat volgens mij vrij uniek zijn :-)

Waar haal je dan je nickname?

[mouse256]

De nickname wordt toch door de join verkregen?
Kan je anders mss eens de structuur van de database geven en wat er exact moet geselecteerd worden, ik denk dat er niemand exact weet wat je wil hebben.

[BlackShadow]

De site is een soort profielen site, ieder lid kan foto's uploaden, de filename enz worden opgeslagen in de tabel WWW_pics.
Verder kan elk lid een profielfoto kiezen uit hun collectie foto's, het id van de gekozen foto wordt bijgehouden in de cel "profielfoto" in de tabel WWW_members.

WWW_members:
id (uniek veld, auto inc)
nickname (uniek veld)
profielfoto (id)

WWW_pics:
id (uniek veld, auto inc)
memberid
height
width
...

Ik zou graag een lijstje krijgen van 6 leden+hun profielfoto, gesorteerd op hun aantal foto's die ze hebben.
Kortweg: de 6 leden met de meeste foto's

Ik dacht:
Meeste foto's = count(WWW_pics.id) as c
Per lid = (GROUP BY WWW_members.nickname)
sorteren = ORDER BY c
Profielfoto ophalen = JOIN

Maar ik krijg een resultaat die totaal niet gesorteerd is op het aantal foto's die ze bezitten

[ Voor 10% gewijzigd door BlackShadow op 21-01-2005 16:04 ]

[bigbeng]

Vergis ik me nu of heeft c altijd de waarde 1 bij jouw resultaten? Je join beperkt namelijk de opgehaalde foto's altijd tot profielfotos en dat is er toch altijd maar 1tje per user?

[Verwijderd]

Volgens mij vergis je je nu.
Zijn users hebben namelijk een fotoboek en daar mogen zij 1 pic uit selecteren om in hun profiel te zetten.

[BlackShadow]

De users hebben allemaal een fotoalbum en een informatiepagina
De bedoeling is dat elke gebruiker wordt getoont adhv hun foto en niet enkel een naam.
Die foto kan men kiezen uit hun geuploade foto's.

BVB: andere users kunnen ook punten geven op de foto's in het fotoalbum van een andere user.

Deze sql string gebruik ik bvb om de foto's met de hoogste score weer te geven

code:

SELECT(COUNT(points)*AVG(points)) AS som, photoID as id, WWW_pics.filename, WWW_members.nickname 
FROM WWW_photoratings 
LEFT  JOIN WWW_pics ON WWW_photoratings.photoID = WWW_pics.id 
LEFT  JOIN WWW_members ON WWW_pics.memberID = WWW_members.id 
GROUP  BY photoID 
ORDER  BY som DESC LIMIT 6

Dit werkt allemaal perfect.

[ Voor 58% gewijzigd door BlackShadow op 21-01-2005 16:24 ]

[bigbeng]

Verwijderd schreef op vrijdag 21 januari 2005 @ 16:20:
Volgens mij vergis je je nu.
Zijn users hebben namelijk een fotoboek en daar mogen zij 1 pic uit selecteren om in hun profiel te zetten.

Ik doelde op deze join voorwaarde:

code:

LEFT JOIN WWW_pics
ON WWW_members.profielfoto = WWW_pics.id

Die beperkt de regels waarover geaggregeerd wordt tot 1 per member (denk ik)

edit:
Per ongeluk de verkeerde gecopied, maar het idee blijft hetzelfde

[ Voor 9% gewijzigd door bigbeng op 21-01-2005 16:25 ]

[Verwijderd]

bigbeng schreef op vrijdag 21 januari 2005 @ 16:23:
[...]


Ik doelde op deze join voorwaarde:

code:

LEFT JOIN WWW_pics
ON WWW_members.profielfoto = WWW_pics.id

Die beperkt de regels waarover geaggregeerd wordt tot 1 per member (denk ik)

edit:
Per ongeluk de verkeerde gecopied, maar het idee blijft hetzelfde

Ja, je hebt gelijk.
Ik denk dat hij wil Joinen op de meberid's .

[BlackShadow]

Verwijderd schreef op vrijdag 21 januari 2005 @ 16:26:
[...]


Ja, je hebt gelijk.
Ik denk dat hij wil Joinen op de meberid's .

Kan je me dan uitleggen hoe ik er moet voor zorgen dat er per member gekeken wordt en niet per foto? of zie ik het allemaal verkeerd?

[mouse256]

Nu ik de structuur van je database heb denk ik dat het dit moet zijn:
werkt, maar geeft verkeerde profielfoto (wat logisch is)

code:

SELECT www_members.nickname,www_pics.filename, count(*) as c FROM www_pics
    INNER JOIN www_members on WWW_members.id = www_pics.memberid
    GROUP BY WWW_pics.memberid
    ORDER BY c DESC

lijkt dus niet te werken:

code:

SELECT www_members.nickname,pics2.filename, count(*) as c FROM www_pics
    INNER JOIN www_members on WWW_members.id = www_pics.memberid
    INNER JOIN www_pics as pics2 on www_pics.id = www_members.profielfoto
    GROUP BY WWW_pics.memberid
    ORDER BY c DESC

edit: hmm, om nu de juiste profielfoto nog te laten weergeven vind ik geen betere oplossing dan de zelfde tabel nog een keer te joinen, ik denk wel dat dit gaan werken maar heb de twijfels of dit wel de beste oplossing is

edit2: je moet dus andere manier verzinnen om de juiste profielfoto te selecteren. Het eenvoudigste is 2de query, maar dat is verre van idiaal omdat je die dan in een lus gaat moeten uitvoeren, ik weet niet direct een oplossing, iemand anders?

[ Voor 73% gewijzigd door mouse256 op 21-01-2005 17:03 ]

[BlackShadow]

Je vorige oplossing werkte perfect, alleen werd de (juiste) profielfoto niet weergegeven...
Die laatste qry die je geeft stuurt weer de output in de war

Pffff, die eerste doet het perfect behalve de profielfoto, ik kom er niet meer uit...

[ Voor 28% gewijzigd door BlackShadow op 21-01-2005 17:09 ]

[mouse256]

Die group by zat de 2de inner join in de war sturen vrees ik
(Ik moet toegeven dat ik er ook nog geen expert in ben, ik heb gisteren voor de eerste keer group by gebruikt )
Ik denk dat je het met een subquery zal moeten gaan oplossen, maar ik heb er helaas geen idee van hoe het werkt en je hebt er mysql 4 (of zelf 4.1 denk ik) voor nodig (en dit is ook de reden waarom ik het nog niet ken, mijn host draait nog mysql 3)

edit:
what about:

code:

SELECT www_members.nickname, count(*) as c,
SUBSTRING(MAX(CONCAT(www_pics.id = www_members.profielfoto,www_pics.filename)),2) as filename FROM www_pics
    INNER JOIN www_members on WWW_members.id = www_pics.memberid
    GROUP BY WWW_pics.memberid
    ORDER BY c DESC

ik hoop dat er geen errors in de code zitten

edit:
ik zag al een haakje te kort
als het werkt wil ik nog wel uitleggen wat dit doet ook

[ Voor 41% gewijzigd door mouse256 op 21-01-2005 17:49 ]

[BlackShadow]

mouse256 schreef op vrijdag 21 januari 2005 @ 17:22:
Die group by zat de 2de inner join in de war sturen vrees ik
(Ik moet toegeven dat ik er ook nog geen expert in ben, ik heb gisteren voor de eerste keer group by gebruikt )
Ik denk dat je het met een subquery zal moeten gaan oplossen, maar ik heb er helaas geen idee van hoe het werkt en je hebt er mysql 4 (of zelf 4.1 denk ik) voor nodig (en dit is ook de reden waarom ik het nog niet ken, mijn host draait nog mysql 3)

edit:
what about:
code...
ik hoop dat er geen errors in de code zitten

edit:
ik zag al een haakje te kort

You have an error in your SQL syntax near 'SUBSTRING(MAX(CONCAT(WWW_pics.id = WWW_members.profielfoto, WWW_pics.filename)),'

[ Voor 16% gewijzigd door BlackShadow op 21-01-2005 18:18 ]

[mouse256]

probeer hem nu nog eens, de from stond fout voor de max(concat... toestand

[BlackShadow]

mouse256 schreef op vrijdag 21 januari 2005 @ 17:48:
probeer hem nu nog eens, de from stond fout voor de max(concat... toestand

Geeft geen fout meer maar ik zie geen profielfoto...
Ik ben helemaal niet meer mee met deze code...

[mouse256]

krijg je een verkeerde output of krijg je helemaal niets?
Kan je anders de output eens posten met en zonder die max(concat(... regels als dit niet te groot is?

[BlackShadow]

Kheb hem in orde gekregen, hartelijk bedankt voor je tijd en moeite!!!

[mouse256]

Zou je dan nog ff willen zeggen wat je er aan hebt liggen veranderen, dan kan ik er ook nog wat van leren

btw: op de mysql site staat wel dat deze max-concat-hack (zo noemt dat dus) eigenlijk verre van ideaal is, en dat die in mysql 4 dus veel beter kan vervangen worden door een subquery. Maar nogmaals: ik zou dus niet weten hoe het moet...

[BlackShadow]

mouse256 schreef op vrijdag 21 januari 2005 @ 18:16:
Zou je dan nog ff willen zeggen wat je er aan hebt liggen veranderen, dan kan ik er ook nog wat van leren

btw: op de mysql site staat wel dat deze max-concat-hack (zo noemt dat dus) eigenlijk verre van ideaal is, en dat die in mysql 4 dus veel beter kan vervangen worden door een subquery. Maar nogmaals: ik zou dus niet weten hoe het moet...

Er is niet veel verandert, alleen WWW_members.profielfoto achter de select nog gezet

Kan je ook even zeggen wat die concat enzo dan allemaal doen?

[ Voor 6% gewijzigd door BlackShadow op 21-01-2005 18:19 ]

[mouse256]

hmm, das dan eerder toeval denk ik dat het goed werkt, je weet namelijk niet welke rijen mysql ligt te groeperen...
Ik blijf het raar vinden dat www_users.profielfoto niet hetzelfde geeft als filename, maar ik kan me vergissen natuurlijk.
Als je gewoon www_users.profielfoto bij de select hebt bijgezet heet normaal die max... geen zin meer en zou die dus eigenlijk weg mogen.

de uitleg:

code:

SUBSTRING(MAX(CONCAT(www_pics.id = www_members.profielfoto,www_pics.filename)),2) as filename

eerst vergelijk je www_pics.id met www_members.profielfoto, je wilt diegene hebben waar dit immers gelijk is. Als dit gelijk is maakt hij er een 1 van, anders een nul, en hij slaagt dit in een soort temporary tabel op. als toetje plak je er via concat de filename achter.
Je krijgt dus een soort tabel met:
0eerstefoto.jpg
1dejuistefoto.jpg
0nogeenfoto.jpg
0ennogeen.jpg

Nu kan je dus mooi met max de rij er uit halen die met de 1 begint, hier zit de juiste filename in, en je hebt nu nog maar 1 rij over. Enkel zit die 1 nu nog in de weg, en die knip je er dan weer uit met substring(...,2)

Ik hoop dat dit duidelijk is?

[ Voor 51% gewijzigd door mouse256 op 21-01-2005 18:24 ]

[BlackShadow]

mouse256 schreef op vrijdag 21 januari 2005 @ 18:21:
hmm, das dan eerder toeval denk ik dat het goed werkt, je weet namelijk niet welke rijen mysql ligt te groeperen...
Ik blijf het raar vinden dat www_users.profielfoto niet hetzelfde geeft als filename, maar ik kan me vergissen natuurlijk.
Als je gewoon www_users.profielfoto bij de select hebt bijgezet heet normaal die max... geen zin meer en zou die dus eigenlijk weg mogen.

de uitleg:

code:

SUBSTRING(MAX(CONCAT(www_pics.id = www_members.profielfoto,www_pics.filename)),2) as filename

eerst vergelijk je www_pics.id met www_members.profielfoto, je wilt diegene hebben waar dit immers gelijk is. Als dit gelijk is maakt hij er een 1 van, anders een nul, en hij slaagt dit in een soort temporary tabel op. als toetje plak je er via concat de filename achter.
Je krijgt dus een soort tabel met:
0eerstefoto.jpg
1dejuistefoto.jpg
0nogeenfoto.jpg
0ennogeen.jpg

Nu kan je dus mooi met max de rij er uit halen die met de 1 begint, hier zit de juiste filename in, en je hebt nu nog maar 1 rij over. Enkel zit die 1 nu nog in de weg, en die knip je er dan weer uit met substring(...,2)

Ik hoop dat dit duidelijk is?

't is idd duidelijk
Leuke constructie

Maareuh, vooral bedankt voor je hulp

[flipm0]

Ik weet niet of het in MySQL werkt, maar je zou dit eens kunnen proberen

code:

SELECT      wm.nickname
,       wp1.width
,       wp1.height
,       wp1.filename
,       count(wp2.id) as c
FROM        WWW_members wm
,       WWW_pics wp1
,       WWW_pics wp2
WHERE       wm.profielfoto = wp1.id
AND     wp2.memberid = wm.id
GROUP BY    wm.nickname
,       wp1.width
,       wp1.height
,       wp1.filename
ORDER BY    c DESC
LIMIT       6

[BlackShadow]

flipm0 schreef op vrijdag 21 januari 2005 @ 23:15:
Ik weet niet of het in MySQL werkt, maar je zou dit eens kunnen proberen

code:

SELECT      wm.nickname
,       wp1.width
,       wp1.height
,       wp1.filename
,       count(wp2.id) as c
FROM        WWW_members wm
,       WWW_pics wp1
,       WWW_pics wp2
WHERE       wm.profielfoto = wp1.id
AND     wp2.memberid = wm.id
GROUP BY    wm.nickname
,       wp1.width
,       wp1.height
,       wp1.filename
ORDER BY    c DESC
LIMIT       6

Propleempje is dat ik profielfoto ook nodig heb in mijn select.
Er wordt gecontroleerd of profielfoto verschillend is van 0, anders wordt er een default image getoond.

[flipm0]

BlackShadow schreef op zaterdag 22 januari 2005 @ 22:07:
[...]


Propleempje is dat ik profielfoto ook nodig heb in mijn select.
Er wordt gecontroleerd of profielfoto verschillend is van 0, anders wordt er een default image getoond.

Die moet je dan ook opnemen in je query, dus je query zou dan iets worden als:

code:

SELECT      wm.nickname
,       wm.profielfoto
,       wp1.width
,       wp1.height
,       wp1.filename
,       count(wp2.id) as c
FROM        WWW_members wm
,       WWW_pics wp1
,       WWW_pics wp2
WHERE       wm.profielfoto = wp1.id
AND     wp2.memberid = wm.id
GROUP BY        wm.nickname
,       wm.profielfoto
,       wp1.width
,       wp1.height
,       wp1.filename
ORDER BY    c DESC
LIMIT       6