[mysql] punten optellen

[Verwijderd]

Ik ben momenteel een soort vragenspel aan het maken.
Voor iedere vraag zijn er meerdere antwoordmogelijkheden, waarvan er 1 juist is.
Per goed antwoord kunnen er punten verdeeld worden. Niet ieder antwoord is evenveel punten waar,
dus per antwoord wordt in de tabel bijgehouden of dit het juiste antwoord is (1) en hoeveel punten
het antwoord waard is.

Nu wordt er in de gebruikerstabel ook een apart veld bijgehouden met punten.
Want gebruikers kunnen ook nog op een andere manier punten halen (buiten het systeem om).

Zo zien de tabellen eruit:

tblUsers
lngUserID
lngU_Punten

tblAntwoorden
lngAntwoordID
lngA_VraagID
txtA_Antwoord
blnA_CorrectAntwoord
lngA_Punten

De antwoorden die door de gebruikers zijn gegeven worden in een koppeltabel bijgehouden:

tblDeelnemer_Vraag
lngDeelnemerVraagID
lngDV_UserID
lngDV_VraagID
lngDV_AntwoordID

Nu wil ik dus een ranglijst maken en de punten moeten dus bij elkaar worden opgeteld.
En dit moet dus als volgt gaan:
- Zoek alle goede antwoorden per gebruiker op en tel de punten bij elkaar op [dit werkt]
- Tel het totaal alle goede antwoorden op bij de punten die de gebruiker buiten het systeem om heeft gehaald (lngU_Punten)

Dat laatste krijg ik niet voor elkaar!

Ik krijg dus wel voor elkaar dat netjes alle goede antwoorden per gebruiker worden opgeteld.
Dit heb ik voor elkaar met de volgende query (wellicht kan het ook anders en sneller?):

PHP:

$qry = 
"SELECT 
        U.`txtU_Achternaam`, 
        U.`lngU_Punten`, 
        SUM(A.`lngA_Punten`) AS antwoordenTotaal 
FROM 
        `tblUsers` U 
RIGHT JOIN 
        `tblDeelnemer_Vraag`    DV ON               
        (DV.`lngDV_UserID`      = U.`lngUserID`)  
        RIGHT JOIN 
            `tblAntwoorden`         A ON 
            (DV.`lngDV_AntwoordID`  = A.`lngAntwoordID`) AND 
            (A.`blnA_CorrectAntwoord` = 1) 
GROUP BY 
        `lngUserID`'";

Maar hoe krijg ik het nu voor elkaar dat lngU_Punten slechts 1x wordt opgeteld bij antwoordenTotaal?!

[P_de_B]

Welke versie van MySQL heb je? Dit in verband met het wel of niet ondersteunen van subqueries.

[Verwijderd]

Sorry, vergeten te vermelden [stom!] : 4.0.14

Damned, waarom kom je altijd tot de oplossing zodra je een topic hebt geopend?!
Heb er echt een paar uur in zitten en opeens lukt het.

PHP:

$sql->execute('SELECT 
                    U.`txtU_Achternaam`, 
                    U.`lngU_Punten`, 
                    SUM(A.`lngA_Punten`) AS antwoordenTotaal, 
                    SUM(A.`lngA_Punten`)+U.`lngU_Punten` AS puntenTotaal 
               FROM 
                    `tblUsers` U 
               RIGHT JOIN 
                    `tblDeelnemer_Vraag`    DV ON               
                    (DV.`lngDV_UserID`      = U.`lngUserID`)  
                    RIGHT JOIN 
                        `tblAntwoorden`         A ON 
                        (DV.`lngDV_AntwoordID`  = A.`lngAntwoordID`) AND 
                        (A.`blnA_CorrectAntwoord` = 1) 
               WHERE 
                    U.`lngU_Level` = 0 
               GROUP BY 
                    U.`lngUserID`, U.`lngU_Punten`');

Mocht iemand een kortere, betere versie weten, dan hoor / lees ik het graag!

[ Voor 91% gewijzigd door Verwijderd op 16-08-2005 10:08 ]

[P_de_B]

SQL:

SELECT lngUserId, SUM(lngA_Punten) + lngU_Punten as TotaalScore
FROM tblUsers U
INNER JOIN tblDeelNemer_Vraag DV ON U.lngUserId = lngDV_UserID
INNER JOIN tblAntwoorden A ON DV.lngDV_AntwoordId = A.lngAntwoordID AND DV.lngDV_VraagID = lngA_VraagID
WHERE A.blnA_CorrectAntwoord = 1
GROUP BY lngUserId, lngU_Punten

Dit moet volgens mij werken

ik had nog niet het venster gerefreshed.

offtopic:
Vind je het echt handig het datatype in de kolomnaam te vermelden?

[ Voor 6% gewijzigd door P_de_B op 16-08-2005 11:22 ]

[Verwijderd]

Dank je, maar wat is nu het wezenlijke verschil? (behalve inner en left)

Ehja, zo heb ik dat geleerd ooit. Ik vind het idd wel handig

[P_de_B]

Verwijderd schreef op dinsdag 16 augustus 2005 @ 11:25:
Dank je, maar wat is nu het wezenlijke verschil? (behalve inner en left)

Niks, maar zie mijn edit die met begint

[Verwijderd]

ok dan, nu ga ik ermee verder. Heb het nu zo gekregen dat ik van alle deelnemers de score krijgt, dus ook foute scores (0 punten). Dat wordt al een net lijstje.

Nu gaan we dus verder ... een deelnemer kan ook nog in een team zitten en nu moeten de totalen per team berekend worden, heerlijk!

De uiteindelijke query voor de deelnemers ranglijst:

PHP:

$sql->execute('SELECT 
                    U.`lngUserID`, 
                    U.`txtU_Voornaam`,
                    U.`txtU_Tussenvoegsel`,
                    U.`txtU_Achternaam`, 
                    U.`lngU_Punten`, 
                    SUM(A.`lngA_Punten`) AS antwoordenTotaal, 
                    SUM(A.`lngA_Punten`)+U.`lngU_Punten` AS puntenTotaal 
               FROM 
                    `tblUsers` U 
               LEFT JOIN 
                    `tblDeelnemer_Vraag`    DV ON               
                    (DV.`lngDV_UserID`      = U.`lngUserID`)  
                    LEFT JOIN 
                        `tblAntwoorden`         A ON 
                        (DV.`lngDV_AntwoordID`  = A.`lngAntwoordID`) AND 
                        (A.`blnA_CorrectAntwoord` = 1) 
               WHERE 
                    U.`lngU_Level` = 0 
               GROUP BY 
                    U.`lngUserID`, U.`lngU_Punten` 
               ORDER BY 
                    puntenTotaal DESC , 
                    antwoordenTotaal DESC, 
                    U.`lngU_Punten` DESC, 
                    U.`txtU_Achternaam` ASC');

[ Voor 60% gewijzigd door Verwijderd op 16-08-2005 11:33 ]

[NMe]

offtopic:
Wat een rare manier van werken. Om de ene identifier zet je wel backticks ( ` dus), om de andere weer niet. De ene keer gebruik je bij een alias wel AS, de andere keer weer niet. Dat lijkt me persoonlijk vrij onduidelijk, waarom gebruik je niet één vaste stijl?

[Verwijderd]

Als je aan kunt geven waar je precies bedoelt, dan kan ik mn manier van werken misschien verbeteren?

U.`kolomnaam` is toch vrijnormaal
En je hoeft toch ook niet voor ieder kolom of tabel een AS te doen, alleen als dat nodig is?!
Ik vind dit zelf allemaal vrij duidelijk leesbaar.
Maar hoe zou jij deze query opstellen dan? Wellicht leer ik er iets van!

NME, jij bedoelt meer zo?:

PHP:

$sql->execute('SELECT 
                    `U`.`lngUserID`, 
                    `U`.`txtU_Voornaam`,
                    `U`.`txtU_Tussenvoegsel`,
                    `U`.`txtU_Achternaam`, 
                    `U`.`lngU_Punten`, 
                    SUM(`A`.`lngA_Punten`)                      `antwoordenTotaal`, 
                    SUM(`A`.`lngA_Punten`)+`U`.`lngU_Punten`    `puntenTotaal` 
               FROM 
                    `tblUsers` `U` 
               LEFT JOIN 
                    `tblDeelnemer_Vraag` `DV` ON                
                    (`DV`.`lngDV_UserID` = `U`.`lngUserID`)  
                    LEFT JOIN 
                        `tblAntwoorden` `A` ON 
                        (`DV`.`lngDV_AntwoordID`      = `A`.`lngAntwoordID`) AND 
                        (`A`.`blnA_CorrectAntwoord` = 1) 
               WHERE 
                    `U`.`lngU_Level` = 0 
               GROUP BY 
                    `U`.`lngUserID`, 
                    `U`.`lngU_Punten` 
               ORDER BY 
                    `puntenTotaal`      DESC , 
                    `antwoordenTotaal`  DESC, 
                    `U`.`lngU_Punten`   DESC, 
                    `U`.`txtU_Achternaam` ASC');

Ik vind dit maar een zooi van backticks
ontopic:
Ik blijf met de query toch een probleem houden.
Namelijk wanneer een deelnemer wel al een aantal punten heeft, maar nog geen vragen heeft beantwoord. SUM geeft dan als resultaat NULL en dit kun je niet optellen bij de punten.

[ Voor 45% gewijzigd door Verwijderd op 16-08-2005 12:05 ]

[whoami]

Heb je niet zoiets als NVL zoals in Oracle, of ISNULL In SQL Server in MySQL ?

[Jerry]

whoami schreef op dinsdag 16 augustus 2005 @ 12:01:
Heb je niet zoiets als NVL zoals in Oracle, of ISNULL In SQL Server in MySQL ?

Mysql: ISNULL()
Link: http://dev.mysql.com/doc/mysql/en/comparison-operators.html

If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

mysql> SELECT ISNULL(1+1);
-> 0
mysql> SELECT ISNULL(1/0);
-> 1

[ Voor 23% gewijzigd door Jerry op 16-08-2005 12:11 ]

[Verwijderd]

Ik krijg het niet voor elkaarom een werkende vergelijking in de query te krijgen.

Heb nu de volgende 2 mogelijkheden geprobeerd, maar icm SUM lijkt het gewoon niet te werken.

vb 1:

code:

CASE NULLIF(SUM(`A`.`lngA_Punten`),1) 
   WHEN 1 
   THEN `U`.`lngU_Punten` `puntenTotaal` 
   ELSE  SUM(`A`.`lngA_Punten`)+`U`.`lngU_Punten` `puntenTotaal` 
END

vb 2:

code:

IF(SUM(`A`.`lngA_Punten`) IS NULL,
`U`.`lngU_Punten`, 
SUM(`A`.`lngA_Punten`)+`U`.`lngU_Punten`) `puntenTotaal`

Heeft iemand enig idee wat ik fout doe? uit de foutmelding van mysql wordt ik niet veel wijzer eerlijk gezegd:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'puntenTotaal`

[P_de_B]

Je moet gewoon de ISNULL gebruiken

SUM(ISNULL(`A`.`lngA_Punten`,0))+`U`.`lngU_Punten`

[Verwijderd]

mmm, ook dat werkt niet hoor ...

SUM(ISNULL(`A`.`lngA_Punten`,0))+`U`.`lngU_Punten` `puntenTotaal`

ISNULL geeft namelijk 1 of 0 terug of ik snap het gewoon echt niet

[ Voor 82% gewijzigd door Verwijderd op 16-08-2005 14:03 ]

[Verwijderd]

Kijk, dit werkt niet:

IF(ISNULL(SUM(`A`.`lngA_Punten`)),
`U`.`lngU_Punten` ,
SUM(`A`.`lngA_Punten`)+`U`.`lngU_Punten`) `puntenTotaal`

en dit weer wel:

IF(ISNULL(NULL),
`U`.`lngU_Punten` ,
SUM(`A`.`lngA_Punten`)+`U`.`lngU_Punten`) `puntenTotaal`

Lijkt wel of die SUM nooit NULL kan opleveren? terwijl dat toch wel zo is

[P_de_B]

Verwijderd schreef op dinsdag 16 augustus 2005 @ 14:12:
Kijk, dit werkt niet:

IF(ISNULL(SUM(`A`.`lngA_Punten`)),
`U`.`lngU_Punten` ,
SUM(`A`.`lngA_Punten`)+`U`.`lngU_Punten`) `puntenTotaal`

en dit weer wel:

IF(ISNULL(NULL),
`U`.`lngU_Punten` ,
SUM(`A`.`lngA_Punten`)+`U`.`lngU_Punten`) `puntenTotaal`

Lijkt wel of die SUM nooit NULL kan opleveren? terwijl dat toch wel zo is

Ik weet niet wat de output van SUM(NULL) is bij MySQL. Misschien maakt hij er 0 van? Anywee, je moet de ISNULL binnen de sum plaatsen toch? SUM(ISNULL(punten,0))

[Verwijderd]

Nou dat gaat zeker niet werken.
IS_NULL(expr) levert 1 of 0
en het is volgens mij ook SUM(expr) en niet SUM(expr,waarde)

SUM([DISTINCT] expr)

Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used as of MySQL 5.0.0 to sum only the distinct values of expr.

[ Voor 45% gewijzigd door Verwijderd op 16-08-2005 14:21 ]

[P_de_B]

Retourneert ISNULL een 1 of 0 bij MySQL ok weer wat geleerd. Ik denk dat je dan COALESCE kunt gebruiken?

SUM(COALESCE(punten,o))

offtopic:
In SQL Server is ISNULL een functie die het tweede argument retourneert, als het eerste NULL is, daarom was ik in de war.

[ Voor 35% gewijzigd door P_de_B op 16-08-2005 14:33 ]

[Verwijderd]

yep op zich logisch toch?

is iets NULL: ja of nee (1 of 0)

Bedankt voor de tip, eindelijk is het gelukt!
COALESCE(SUM(`A`.`lngA_Punten`),0)+COALESCE(`U`.`lngU_Punten`,0) `puntenTotaal`

[ Voor 52% gewijzigd door Verwijderd op 16-08-2005 14:45 ]

[Verwijderd]

Geweldig, per gebruiker lukt het nu om netjes een ranglijst op te stellen.
Maar het wordt nog wat ingewikkelder (voor mij dan).
Een deelnemer zit ook nog in één team.

Hier kom ik dus echt niet uit....

Hier komen de tabellen weer:
tblTeams
lngTeamID
txtT_Naam

tblUsers
lngUserID
lngU_TeamID
lngU_Punten

tblAntwoorden
lngAntwoordID
lngA_VraagID
txtA_Antwoord
blnA_CorrectAntwoord
lngA_Punten

De antwoorden die door de gebruikers zijn gegeven worden in een koppeltabel bijgehouden:

tblDeelnemer_Vraag
lngDeelnemerVraagID
lngDV_UserID
lngDV_VraagID
lngDV_AntwoordID


Nu dacht ik dus het volgende:

PHP:

SELECT 
   T.`txtT_Naam`, 
   U.`lngUserID`,
   U.`lngU_Punten`,
   A.`lngA_Punten`
FROM 
   `tblTeams` T 
LEFT JOIN 
   `tblUsers` U ON 
   (T.`lngTeamID` = U.`lngU_TeamID`) 
   LEFT JOIN 
      `tblDeelnemer_Vraag` DV ON 
      (U.`lngUserID` = DV.`lngDV_UserID`) 
      LEFT JOIN 
         `tblAntwoorden` A ON 
     (DV.`lngDV_AntwoordID` = A.`lngAntwoordID`)

Zo krijg ik dus netjes een lijstje van alle antwoorden die gegeven zijn op de vragen.
Als er voor een team nog geen deelnemers zijn, dan wordt dit team wel getoond, maar zijn de waardes NULL.

Dit is een voorbeeld van het resultaat

txtT_Naam	lngUserID	lngU_Punten	lngA_Punten
Team 1	7	5	1
Team 1	7	5	5
Team 1	8	12	1
Team 2	6	25	5
Team 2	6	25	0
Team 3	null	null	null
Team 4	1	0	null

Nu moet er dus als volgt opgeteld worden:
- Per team moeten alle lngA_Punten worden opgeteld (resultaat 1)
- Per team moet per gebruiker lngU_punten worden opgeteld (resultaat 2)
- Per team moeten resultaat 1 + resultaat 2 opgeteld worden

Uiteindelijk resultaat moet dus zijn:
Team 1 24 punten
Team 2 30 punten
Team 3 0 punten
Team 4 0 punten

Ik krijg dit maar niet voor elkaar.

[ Voor 15% gewijzigd door Verwijderd op 16-08-2005 16:32 . Reden: border="1" of border=1 werkt volgens mij niet in de ubb table tag ]

[P_de_B]

Wat is nu precies je vraag?

Ok, ik zal even wachten tot je klaar bent met editten

[ Voor 56% gewijzigd door P_de_B op 16-08-2005 16:28 ]

[NMe]

Verwijderd schreef op dinsdag 16 augustus 2005 @ 11:59:
Als je aan kunt geven waar je precies bedoelt, dan kan ik mn manier van werken misschien verbeteren?

Met "manier van werken" doelde ik eigenlijk op de manier waarop je je query schreef.

U.`kolomnaam` is toch vrijnormaal

Nee, maar `U`.`kolomnaam` of U.kolomnaam weer wel.

En je hoeft toch ook niet voor ieder kolom of tabel een AS te doen, alleen als dat nodig is?!

Nee, het hoeft niet bij elk veld, maar als ik je query er even bij pak:

SQL:

                    SUM(A.`lngA_Punten`) AS antwoordenTotaal,

Prima, daar gebruik je AS om een alias aan te geven. Maar dan:

SQL:

                       `tblUsers` U

Hier gebruik je ineens weer niet AS om een alias aan te geven. Twee manieren van schrijven voor hetzelfde fenomeen staat warrig en onduidelijk IMO, en daar zou ik wat aan doen. Ik zou dus ofwel bij die regel 4 (en alle andere regels waar je AS gebruikt) de AS weglaten, of, beter, in regel 7 en alle andere regels waar dit opgaat een AS toevoegen.

Ik vind dit zelf allemaal vrij duidelijk leesbaar.
Maar hoe zou jij deze query opstellen dan? Wellicht leer ik er iets van!

NME, jij bedoelt meer zo?:

PHP:

//query

Jup, dat lijkt er al meer op.

Ik vind dit maar een zooi van backticks

Tsja, als je backticks gebruikt, dan vind ik het persoonlijk wel zo netjes om ze overal te gebruiken, of overal niet. Dat staat een stuk netter/overzichtelijker.

[Verwijderd]

Ok dan, verhelderend! Doe het al jaren zo, haha! Weer wat geleerd en ik zal in de toekomst ook zeker proberen me er aan te houden ... maar nu terug naar m'n probleen

[Verwijderd]

/* kick */

Ik kom er echt niet uit.
Ik kom nu wel tot het totaal aantal behaalde punten per deelnemer per team.
Maar ik kan maar niet voor elkaar krijgen om 1x per gebruiker per team de apart verdiende punten (lngU_Punten) op te tellen.

PHP:

$sql->execute('SELECT                                       
                    DISTINCT `U`.`lngU_Punten` AS `eenmaalLossePuntenPerDeelnemerPerTeam`,                   
                    COALESCE(SUM(`A`.`lngA_Punten`),0) AS `totaalVragenPuntenPerDeelnemerPerTeam`, 
                    `U`.`lngUserID`,
                    `T`.`txtT_Naam` 
               FROM 
                    `tblTeams` AS `T` 
               LEFT JOIN 
                    `tblUsers` AS `U` ON 
                    (`T`.`lngTeamID` = `U`.`lngU_TeamID`) 
                    LEFT JOIN 
                        `tblDeelnemer_Vraag` AS `DV` ON 
                        (`U`.`lngUserID` = `DV`.`lngDV_UserID`) 
                        LEFT JOIN 
                            `tblAntwoorden` AS `A` ON 
                            (`DV`.`lngDV_AntwoordID` = `A`.`lngAntwoordID`) 
              GROUP BY 
                    `U`.`lngUserID`');

zo krijg ik dus netjes per team:
- totaal aantal punten per deelnemer behaald voor de vragen (res 1)
- totaal aantal standaard punten per deelnemer (res 2)

Deze wil ik dus bij elkaar optellen, maar dat lukt dus niet.
Ik dacht iets met SUM(DISTINCT maar dat wil dus echt niet werken.
Wordt dit pas vanaf een latere versie van MySQL ondersteund?
En DISTINCT mag je ook niet gebruiken in COALESCE. Want soms kan res2 ook NULL zijn en wil ik daar 0 voor terug hebben.

[P_de_B]

Ik heb er naar gekeken, en volgens mij is er zonder een subquery te gebruiken geen mogelijkheid dit in 1 query te doen. Je zou nog de volgende query in een temp table kunnen gooien, en daaruit een select met een group by op teamId kunnen doen, maar iets anders zou ik niet weten.

SQL:

SELECT  T.lngTeamId, SUM(lngU_punten) as Punten
FROM tblTeams T
INNER JOIN tblUsers U ON t.lngTeamID =U.lngTeamId
GROUP BY T.lngTeamId
UNION
SELECT  T.lngTeamId, SUM(lngA_punten) as Punten
FROM tblTeams T
INNER JOIN tblUsers U ON t.lngTeamID =U.lngTeamId
INNER JOIN tblDeelNemer_Vraag DV ON U.lngUserId = lngDV_UserID
INNER JOIN tblAntwoorden A ON DV.lngDV_AntwoordId = A.lngAntwoordID AND DV.lngDV_VraagID = lngA_VraagID
WHERE A.blnA_CorrectAntwoord = 1
GROUP BY T.lngTeamId

Is er een mogelijkheid MySQL te upgraden naar min. 4.1? Dan kun je subqueries gebruiken en is het niet zo moeilijk.

[Verwijderd]

De query die je hebt gegeven werkt op zich ok ... maar per team moeten dan de Punten nog opgeteld worden

PHP:

SELECT  T.lngTeamID, SUM(lngU_punten) as Punten
FROM tblTeams T
INNER JOIN tblUsers U ON T.lngTeamID =U.lngU_TeamID
GROUP BY T.lngTeamID
UNION
SELECT  T.lngTeamId, SUM(lngA_punten) as Punten
FROM tblTeams T
INNER JOIN tblUsers U ON T.lngTeamID =U.lngU_TeamID
INNER JOIN tblDeelnemer_Vraag DV ON U.lngUserID = lngDV_UserID
INNER JOIN tblAntwoorden A ON DV.lngDV_AntwoordID = A.lngAntwoordID AND DV.lngDV_VraagID = lngA_VraagID
WHERE A.blnA_CorrectAntwoord = 1
GROUP BY T.lngTeamID

[P_de_B]

Verwijderd schreef op woensdag 17 augustus 2005 @ 11:29:
De query die je hebt gegeven werkt op zich ok ... maar per team moeten dan de Punten nog opgeteld worden

PHP:

SELECT  T.lngTeamID, SUM(lngU_punten) as Punten
FROM tblTeams T
INNER JOIN tblUsers U ON T.lngTeamID =U.lngU_TeamID
GROUP BY T.lngTeamID
UNION
SELECT  T.lngTeamId, SUM(lngA_punten) as Punten
FROM tblTeams T
INNER JOIN tblUsers U ON T.lngTeamID =U.lngU_TeamID
INNER JOIN tblDeelnemer_Vraag DV ON U.lngUserID = lngDV_UserID
INNER JOIN tblAntwoorden A ON DV.lngDV_AntwoordID = A.lngAntwoordID AND DV.lngDV_VraagID = lngA_VraagID
WHERE A.blnA_CorrectAntwoord = 1
GROUP BY T.lngTeamID

Dat weet ik, maar dat lukt dus niet. Vandaar mijn voorstel dit resultaat in een temp tabel te doen, en daar de uiteindelijke totalen per team dus uit te halen. Iets anders zou ik niet weten zonder subqueries.

[Verwijderd]

Ik heb de provider gemaild om te vragen of een upgrade mogelijk is...

Zon temptabel lijkt me niet te doen: er gaan hier zon 5000 deelnemers gebruik van maken.
Dat wordt dan een enorme serverload of vergis ik me daar nu in?

[ACM]

Ook met subqueries wordt het een draak van een query lijkt me. Imho kan je beter apart "per groep" en apart "per gebruiker" de gegevens ophalen en dat op applicatieniveau bij elkaar vegen.

Btw, check de leesbaarheid van deze query eens ivt degene die jij geeft:

SQL:

SELECT                                         
   DISTINCT U.User_Punten AS eenmaalLossePuntenPerDeelnemerPerTeam,                      
   COALESCE(SUM(A.Antwoord_Punten), 0) AS totaalVragenPuntenPerDeelnemerPerTeam, 
   U.UserID,
   T.Team_Naam
FROM 
  Teams AS T 
  LEFT JOIN Users AS U ON T.TeamID = U.User_TeamID
  LEFT JOIN Deelnemer_Vraag AS DV ON U.UserID = DV.DV_UserID
  LEFT JOIN Antwoorden AS A ON DV.DV_AntwoordID = A.AntwoordID
GROUP BY 
  U.UserID

Wat gewijzigd aan je indenting, naamgeving en gebruik van enigszins overbodige queryconstructies (` en ( ) Je namen eindigen al met ID, dus weet je al dat het een lng is. Tabellen kan je maar op een beperkt aantal punten gebruiken in een query, dus is het onzin (imho) om er tbl voor te plakken. Die hungarian notation is imho niet bepaald een duidelijke notatie. Bovendien snij je je in je vingers als je ooit views wilt gebruiken zonder je queries aan te passen en je dan dus met jouw naamgeving verplicht bent die view vwTabelnaam te noemen
Backticks zijn verder alleen echt nodig als de string die je ermee omsluit dubbelzinnig is, maar dat komt heel weinig voor in een select-statement vziw. En de haakjes bij LEFT JOIN zijn ook niet strict noodzakelijk. Als je er meerdere statements achter gaat plakken wordt het wel interessant om dat wat duidelijker te groeperen.

[P_de_B]

Verwijderd schreef op woensdag 17 augustus 2005 @ 11:38:
Ik heb de provider gemaild om te vragen of een upgrade mogelijk is...

Zon temptabel lijkt me niet te doen: er gaan hier zon 5000 deelnemers gebruik van maken.
Dat wordt dan een enorme serverload of vergis ik me daar nu in?

Je hebt gelijk een temptable is niet erg goed voor de perfomance. Een ander alternatief is nog aan de clientkant de 2 regels per team uit bovenstaande query (de vaste punten, en de punten uit de antwoorden) op te tellen.

[ACM]

Verwijderd schreef op woensdag 17 augustus 2005 @ 11:38:
Ik heb de provider gemaild om te vragen of een upgrade mogelijk is...

Zon temptabel lijkt me niet te doen: er gaan hier zon 5000 deelnemers gebruik van maken.
Dat wordt dan een enorme serverload of vergis ik me daar nu in?

Een temporary table is helemaal niet zo sloom hoor. Sterker nog, voor veel van die grote "draak queries" wordt op de achtergrond sowieso al een of meer temporary tables gebruikt

Kan je het net zo goed expliciet doen om daarmee een stel lichtere queries te kunnen gebruiken, als dat nodig is. MySQL's (iig < 5.0) performance voor complexe queries is echt een drama, dus de kans is groot dat met een stel lichte queries die een temporary table vullen je uiteindelijk beter af bent.

[ Voor 13% gewijzigd door ACM op 17-08-2005 11:44 ]

[Verwijderd]

Wat ik nog ff niet zie is waarom je de puntenwaarde bij de vraag opslaat.
Krijg je soms ook punten voor foute antwoorden, of krijg je daar per definitie geen punten voor?
In dat geval kun je beter het puntenaantal bij de vraag opslaan, omdat de puntenwaarde bij foute antwoorden dan geen betekenis heeft.

Of, als antwoorden ook 'een beetje' goed kunnen zijn, en dus toch een paar punten op kunnen leveren, heb je de indicatie 'goed/fout' niet nodig.

Zeker in dat laatste geval wordt je query eenvoudiger, want je kunt de selectie op 'alleen de goede' antwoorden eruit halen; je kunt volstaan met het domweg optellen van het puntentotaal van de gekozen antwoorden.

[Verwijderd]

@ACM:

Bedankt voor je duidelijke uitleg.
Op de een of andere manier heb ik deze wijze van noteren aangeleerd.
Of die helemaal fout is, weet ik niet. Volgens mij heb ik dit aan de DB vakken op de Haagse Hogeschool te danken. Voor deze applicatie ga ik echter de notatie niet meer aanpassen.
Maar in toekomstige applicaties zeker wel!

Ik denk dat ik idd, maar verder ga borduren op de applicatie kant.
Alles per team ophalen, totalen optellen, in een array gooien en sorteren.

[Verwijderd]

@mGerben:

Momenteel is er slechts 1 goed antwoord mogelijk.
Maar per definitie is niet elk antwoord helemaal fout en je kunt dus per antwoord een verschillend aantal punten krijgen.

Net als vroeger bij wiskunde: als de wijze van berekenen goed is, maar je komt op verkeerde getallen uit, verdien je toch punten.

[ACM]

Het kan trouwens wel met één query.

Je zit nu met het probleem dat je groepen hebt en daar weer meerdere gebruikers in hebt. Je krijgt dan bij de naive implementatie of per groep je gegevens of per gebruiker.
Als je echter die twee echter "kwadrateert" kan je steeds voor de losse gebruikers de goede eruit filteren en voor de groepen gewoon delen door het aantal gebruikers.

Je komt dan dus bij SQL's standaard join gedrag uit, namelijk een cross join. Ik heb geen zin het helemaal met jouw relatief complexe structuur uit te werken, maar hier een proof of concept:

groepen: grid, grnaam
gebruikers: gid, gnaam, grid
antwoorden: gid, aid, score

SQL:

SELECT
   gr.grid,
   gr.gnaam,
   -- score per groep, optellen en delen door aantal gebruikers
   -- vanwege de kwadratering van het aantal records door de cross-join
   SUM(a.score) / COUNT(DISTINCT(g1.gid)) as groep_score,
   -- dit wordt een soort impliciete join van g1 op gcross, om zo eenmalig alle 
   -- "geexplodeerde" records af te gaan, ipv voor elk 
   -- gcross-record elke g1-record aflopen
   -- de MAX is om te forceren dat er de goede waarde eruit komt, en zorgt voor makkelijkere groeping
   -- MAX(null, null, null, 1, null, null) = 1, maar je moet wel duidelijk maken dat je die 1 wilt ;)
   MAX(CASE WHEN g1.gid = gcross.gid THEN g1.gid ELSE NULL END) as gid,
   MAX(CASE WHEN g1.gid = gcross.gid THEN g1.gnaam ELSE NULL END) as gnaam,
   SUM(CASE WHEN g1.gid = gcross.gid THEN a.score ELSE NULL END) as gebruikers_score
FROM 
   groepen gr
   -- de g1 en gcross vormen samen een cross-join hier
   LEFT JOIN gebruikers g1 ON gr.grid = g1.grid
   LEFT JOIN antwoorden a ON g1.gid = a.gid
   LEFT JOIN gebruikers gcross ON gr.grid = gcross.grid
GROUP BY
  gr.grid, gr.grnaam
ORDER BY
  groeps_score, gebruikers_score

Over de performance in MySQL durf ik niks te zeggen echt complex is de query niet, dus het zal niet eens erg tegenvallen, test het eens

[ Voor 7% gewijzigd door ACM op 17-08-2005 15:18 . Reden: Distinct erbij, zie P_de_B wat omlaag ]

[Verwijderd]

thanks, ik zie dat ik nog een hoop query toestanden te leren heb!
Heb me in 4 jaar tijd toch altijd met relatief eenvoudige queries kunnen behelpen.

[P_de_B]

ACM, weet je zeker dat je voorbeeld klopt? Volgens mij levert de eenvoudiger query:

SQL:

SELECT
     gr.grid, gr.grnaam, SUM(a.score)/ count(g1.gid) as groep_score, MAX(gnaam) as gnaam, SUM(a.score) as gebruikers_score
FROM 
   groepen gr
   LEFT JOIN gebruikers g1 ON gr.grid = g1.grid
   LEFT JOIN antwoorden a ON g1.gid = a.gid
GROUP BY  gr.grid, gr.grnaam

hetzelfde resultaat op, maar beide niet het gewenste resultaat.

Misschien staar ik me er wel helemaal blind op, en sla ik me straks voor m'n kop dat ik dit post maar ik zie het niet.

Ik heb de volgende testdata gebruikt:

SQL:

CREATE TABLE groepen (grid int, grnaam varchar(10))
CREATE TABLE gebruikers (gid int, gnaam varchar(10), grid int)
CREATE TABLE antwoorden (gid int, aid int, score int)

INSERT INTO groepen (grid, grnaam) VALUES (1,'Groep1')
INSERT INTO groepen (grid, grnaam) VALUES (2,'Groep2')
INSERT INTO groepen (grid, grnaam) VALUES (3,'Groep3')

INSERT INTO gebruikers (gid, gnaam, grid) VALUES (1, 'Gebruiker1', 1)
INSERT INTO gebruikers (gid, gnaam, grid) VALUES (2, 'Gebruiker2', 1)
INSERT INTO gebruikers (gid, gnaam, grid) VALUES (3, 'Gebruiker3', 2)
INSERT INTO gebruikers (gid, gnaam, grid) VALUES (4, 'Gebruiker4', 2)

INSERT INTO antwoorden (gid, aid, score) VALUES (1,1,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (1,2,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (1,3,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (2,1,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (2,2,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (2,3,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (3,1,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (3,2,1)

en de output van beide queries is:

1	Groep1	1	Gebruiker2	6
2	Groep2	0	Gebruiker4	2
3	Groep3	NULL	NULL	NULL

Moet het niet het volgende zijn:

SQL:

SELECT
   gr.grid,
   gr.grnaam,
   SUM(a.score) / COUNT(DISTINCT gcross.gid) as groep_score,
   MAX(CASE WHEN g1.gid = gcross.gid THEN g1.gnaam ELSE NULL END) as gnaam,
   SUM(CASE WHEN g1.gid = gcross.gid THEN a.score ELSE NULL END) as gebruikers_score
FROM 
   groepen gr
   LEFT JOIN gebruikers g1 ON gr.grid = g1.grid
   LEFT JOIN antwoorden a ON g1.gid = a.gid
   LEFT JOIN gebruikers gcross ON gr.grid = gcross.grid
GROUP BY
  gr.grid, gr.grnaam,gcross.gnaam

Dus, DISTINCT in de count, en ook groeperen op gnaam? Deze query heeft als output:

1	Groep1	6	Gebruiker1	3
1	Groep1	6	Gebruiker2	3
2	Groep2	2	Gebruiker3	2
2	Groep2	2	Gebruiker4	NULL
3	Groep3	NULL	NULL	NULL

[ACM]

Ja, inderdaad. Die distinct moet er bij

Jouw query kan per definitie niet werken, maar de mijne doet nu idd voor elke gcros.gid elke g1.gid tellen. Ipv alleen de g1.gid's die er daadwerkelijk zijn.

[P_de_B]

ACM schreef op woensdag 17 augustus 2005 @ 15:17:

Jouw query kan per definitie niet werken.

Bedoel je nu mijn laatste query?

[ACM]

P_de_B schreef op woensdag 17 augustus 2005 @ 15:19:
Bedoel je nu mijn laatste query?

Nee, natuurlijk niet, want dat is mijnes+distinct Je eerste in die post, bedoelde ik, maar ik geloof dat ik eroverheen las dat je die alleen als illustratie bedoelde

[P_de_B]

ACM schreef op woensdag 17 augustus 2005 @ 16:26:
[...]

Nee, natuurlijk niet, want dat is mijnes+distinct Je eerste in die post, bedoelde ik, maar ik geloof dat ik eroverheen las dat je die alleen als illustratie bedoelde

Heh, ok ik dacht al, "dat is zijnes + distinct"

[ACM]

Magoed, ook jouw aangepaste versie was nog niet correct. Je kreeg bij een gebruiker die geen scores zelf heeft geen groepsscore en dat is in principe fout.

Dat aanpassen was nog best een klus, maar volgens mij is dit hem nu helemaal:

SQL:

SELECT
   gr.grid,
   gr.grnaam,
   g1.gid,
   g1.gnaam,
   SUM(across.score) / 
         -- groepsscore moet gecorrigeerd worden 
         -- voor het aantal scores voor _deze_ gebruiker
         -- Als er geen resultaten van deze gebruiker zijn, door 1 delen
         COALESCE(NULLIF(COUNT(DISTINCT(a.aid)), 0), 1) as groep_score,
   SUM(a.score) /
         -- Terwijl de gebruikersscore moet worden aangepast voor het aantal 
         -- "groepsscores" (en dat is gelijk aan het aantal opgegeven scores
         -- gedeeld door het aantal eigen scores -> hier wordt vermenigvuldigd met 
         -- het omgekeerde om een deling door 0 te voorkomen).
         COUNT(across.aid) * COUNT(DISTINCT(a.aid)) as gebruikers_score
FROM 
   groepen gr
   LEFT JOIN gebruikers g1 ON gr.grid = g1.grid
   LEFT JOIN antwoorden a ON g1.gid = a.gid
   LEFT JOIN gebruikers gcross ON gr.grid = gcross.grid
   LEFT JOIN antwoorden across ON gcross.gid = across.gid
GROUP BY
  gr.grid, gr.grnaam, g1.gid, g1.gnaam
ORDER BY
  groep_score DESC, gebruikers_score DESC;

Dit gaat er wel van uit dat het aantal keren dat een gebruiker op 1 vraag antwoord gaf ook maximaal 1 is. Als iemand een vraag vaker mag beantwoorden en de punten bij elkaar opgeteld moeten worden is deze query weer flawed.

[ Voor 10% gewijzigd door ACM op 17-08-2005 17:13 ]

[Verwijderd]

En dat klopt, de gebruiker mag een vraag maar 1x beantwoorden
Ik heb nu toch even de oude aanpak genomen, want hier worden nu alleen de antwoorden berekent en nog niet de vaste punten van een gebruiker

[P_de_B]

ACM schreef op woensdag 17 augustus 2005 @ 17:06:
Magoed, ook jouw aangepaste versie was nog niet correct. Je kreeg bij een gebruiker die geen scores zelf heeft geen groepsscore en dat is in principe fout.

Ok, daar heb je een punt. Ik krijg echter wel resultaten voor een groep als de gebruiker geen antwoorden heeft gegeven met mijn laatse query. Gaat SQL Server misschien anders om met het testen NULLs? Ik kan op dit moment niet testen in MySQL.

Ook zie ik volgens ook nog een foutje in jouw query. Als in een groep 2 gebruikers zitten, en 1 van die gebruikers heeft nog geen antwoorden gegeven worden de gebruikers_scores van de andere gebruiker niet goed berekend.

Met de volgende testdata kun je zien dat gebruiker 5 2 punten bij de gebruikersscore heeft, terwijl hij maar 1 vraag goed heeft beantwoordt.

SQL:

INSERT INTO groepen (grid, grnaam) VALUES (1,'Groep1')
INSERT INTO groepen (grid, grnaam) VALUES (2,'Groep2')
INSERT INTO groepen (grid, grnaam) VALUES (3,'Groep3') 

INSERT INTO gebruikers (gid, gnaam, grid) VALUES (1, 'Gebruiker1', 1)
INSERT INTO gebruikers (gid, gnaam, grid) VALUES (2, 'Gebruiker2', 1)
INSERT INTO gebruikers (gid, gnaam, grid) VALUES (3, 'Gebruiker3', 2)
INSERT INTO gebruikers (gid, gnaam, grid) VALUES (4, 'Gebruiker4', 2) 
INSERT INTO gebruikers (gid, gnaam, grid) VALUES (5, 'Gebruiker5', 3)
INSERT INTO gebruikers (gid, gnaam, grid) VALUES (6, 'Gebruiker6', 3)

INSERT INTO antwoorden (gid, aid, score) VALUES (1,1,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (1,2,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (1,3,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (2,1,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (2,2,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (2,3,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (3,1,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (3,2,1)
INSERT INTO antwoorden (gid, aid, score) VALUES (5,2,1) 

Je vermenigvuldigt met de distinct count van a.aid, maar gebruiker 6 komt niet voor in a. Je moet dus nog de volgende wijziging doen volgens mij:

SQL:

SUM(a.score) / 
         -- Terwijl de gebruikersscore moet worden aangepast voor het aantal 
         -- "groepsscores" (en dat is gelijk aan het aantal opgegeven scores
         -- gedeeld door het aantal eigen scores -> hier wordt vermenigvuldigd met 
         -- het omgekeerde om een deling door 0 te voorkomen).
      ,  COUNT(COALESCE(across.aid,0)) * COUNT(DISTINCT(COALESCE(a.aid,0))) 

Een COALESCE om de counts van across en a. Dan gaat het wel goed.

Maar deze laatste variant geeft dus hetzelfde resultaat als de eerder door mij iets aangepast versie van jouw eerste query.:

SQL:

SELECT
   gr.grid,
   gr.grnaam,
   SUM(a.score) / COUNT(DISTINCT gcross.gid) as groep_score,
   MAX(CASE WHEN g1.gid = gcross.gid THEN g1.gnaam ELSE NULL END) as gnaam,
   SUM(CASE WHEN g1.gid = gcross.gid THEN a.score ELSE NULL END) as gebruikers_score
FROM 
   groepen gr
   LEFT JOIN gebruikers g1 ON gr.grid = g1.grid
   LEFT JOIN antwoorden a ON g1.gid = a.gid
   LEFT JOIN gebruikers gcross ON gr.grid = gcross.grid
GROUP BY
  gr.grid, gr.grnaam,gcross.gnaam
ORDER BY 
   gr.grnaam DESC, g1.gnaam DESC;