login  Naam:   Wachtwoord: 
Registreer je!
 Forum

optellen in SQl met IF voorwaarde

Offline markla - 18/11/2012 15:50
Avatar van marklaPHP interesse Voor mijn statistiek database ben ik op zoek naar een methode om het uit een thuis resultaat te presenteren.
Daarbij wil graag weten weten hoeveel doelpunten de door de thuisclub zijn gemaakt en hoeveel door de uit spelende club.

in speek taal zou ik zeggen : als de hometeamid =1 tel dan de homescore_ft bijalkaar op en geef het resultaat terug als homescore.
dus in code:

sql code - Bekijk de code zonder highlighting - Klap code in 
  1. IF(hometeamid =1 SUM(homescore_ft) AS homescore



maar dit werkt zo niet

p.s.

Ik weet dat ik ook
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. WHERE hometeamid =1

kan doen maar ik wil het graag in de IF regel hebben.

Kan dat en zo ja op welke manier moet ik dan de IF..SUM noteren.

13 antwoorden

Gesponsorde links
Offline Gerard - 18/11/2012 16:04 (laatste wijziging 18/11/2012 16:04)
Avatar van Gerard Ouwe rakker Kan je misschien even vanuit MySQL een dumpje maken van je relevante database structuur en gegevens? Op die manier kan ik wat beter proberen wat de beste oplossing is.

Gewoon even op pastebin.be uploaden en de link hier plaatsen.
Offline markla - 18/11/2012 16:38
Avatar van markla PHP interesse ik krijg zou gauw alleen dit voor elkaar kan je daar wat mee?
Tabelstructuur voor tabel `af_games`

php code - Bekijk de code zonder highlighting - Klap code in 
  1. `Game_ID` mediumint(7) unsigned zerofill NOT NULL AUTO_INCREMENT,
  2. `Game_Played` enum('Y','N') NOT NULL DEFAULT 'N',
  3. `Season_ID` int(4) unsigned DEFAULT '0',
  4. `Home_Team_ID` int(4) unsigned DEFAULT '0',
  5. `Away_Team_ID` int(4) unsigned DEFAULT '0',
  6. `Home_Score_FT` int(2) NOT NULL DEFAULT '0',
  7. `Away_Score_FT` int(2) NOT NULL DEFAULT '0',


"Game_ID","Game_Played","Season_ID","Home_Team_ID","Away_Team_ID","Home_Score_FT","Away_Score_FT"
0001118,"Y",99,33,3,1,0
0001119,"Y",99,3,18,1,1
0001120,"Y",99,16,3,2,0
0001121,"Y",99,3,19,1,1
0001122,"Y",99,3,32,1,2
0001123,"Y",99,28,3,1,2
0001124,"Y",99,3,31,0,4
0001125,"Y",99,66,3,1,2
0001126,"Y",99,3,27,1,1
0001127,"Y",99,34,3,5,1
0001128,"Y",99,3,4,1,4
0001129,"Y",99,17,3,4,0
0001130,"Y",99,3,42,1,1
0001131,"Y",99,35,3,2,0
0001132,"Y",99,20,3,4,1
0001133,"Y",99,3,44,0,1
0001134,"Y",99,5,3,0,1
0001135,"Y",99,3,34,1,0
0001136,"Y",99,4,3,4,0
0001137,"Y",99,44,3,1,0
0001138,"Y",99,3,16,3,1
0001139,"Y",99,3,20,2,2
0001140,"Y",99,19,3,0,2
0001141,"Y",99,3,5,2,3
0001142,"Y",99,42,3,1,0
0001143,"Y",99,32,3,0,0
0001144,"Y",99,3,66,1,1
0001145,"Y",99,18,3,5,3
0001146,"Y",99,3,33,1,3
0001147,"Y",99,3,17,1,0
0001148,"Y",99,27,3,1,0
0001149,"Y",99,3,35,1,0
0001150,"Y",99,31,3,3,2
0001151,"Y",99,3,28,4,1
0001236,"Y",99,3,61,1,0
0001980,"Y",99,324,3,2,6
0001981,"Y",99,301,3,0,13
0001982,"Y",99,317,3,0,6
0001983,"Y",99,294,3,1,4
0001984,"Y",99,3,36,0,2
0001985,"Y",99,3,61,5,1
0002106,"Y",99,396,3,1,2
0002107,"Y",99,46,3,4,0
0002125,"Y",99,3,263,1,1
0002184,"Y",99,313,3,1,1
0002185,"Y",99,279,3,1,2
0003214,"Y",99,3,141,1,3
0003215,"Y",99,145,3,2,0
0003216,"Y",99,3,113,2,7
0003383,"Y",99,568,3,0,1
0003579,"Y",99,175,3,4,0
0003580,"Y",99,272,3,2,0
Offline Gerard - 18/11/2012 17:42
Avatar van Gerard Ouwe rakker MySQL Create scripts waren makkelijker geweest. Dan kan ik ze namelijk direct in mijn MySQL gooien en testen. Nu moest ik handmatig de tabellen aanmaken en de data invoeren.

Als ik zo kijk, dan vraag ik me af of je niet gewoon krijgt wat je zou willen met een GROUP BY query. Je krijgt dan een goed overzicht van alle thuisdoelpunten per team.

sql code - Bekijk de code zonder highlighting - Klap code in 
  1. SELECT Home_Team_ID, SUM(Home_Score_FT) AS Home_Score_SUM
  2. FROM af_games
  3. GROUP BY Home_Team_ID


Je zou hier nog een clausule op kunnen zetten dat alleen games worden meegenomen uit het huidige seizoen.
Offline markla - 18/11/2012 18:06
Avatar van markla PHP interesse Gerard

bedankt voor je reacties

die
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. SUM(Home_Score_FT) AS Home_Score_SUM

Had ik zelf ook al geprobeerd.

Maar ik wil eigenlijk net zoiets als waarmee ik de winst/gelijk/verlies status van een wedstrijd bepaal
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. SUM(IF(a.homereamID =1 AND a.homescoreFT>a.awayscoreFT,1,0)) AS home_win


ik hoop dat je begrijpt wat ik bedoel
Offline Gerard - 18/11/2012 18:15
Avatar van Gerard Ouwe rakker Ik begin een gevoel te krijgen voor wat je wilt bereiken, maar de manier hoe je het nu aanpakt is het per wedstrijd georienteerd. Je zou dan eigenlijk vanuit 1 team willen weten hoeveel wedstrijden ze hebben gespeeld (ongeacht thuis of uit) en hoeveel ze hebben gewonnen (, gelijk hebben gespeeld en verloren).

Is dat wat je probeert te bereiken?
Offline markla - 18/11/2012 18:35 (laatste wijziging 18/11/2012 18:35)
Avatar van markla PHP interesse het is (juist) niet per wedstrijd georienteerd

Wat ik wil weten is per seizoen:

thuis: | winst | gelijk | Verlies | score voor | score tegen
uit: | winst | gelijk | Verlies | score voor | score tegen
totaal: | winst | gelijk | Verlies | score voor | score tegen

En bij de
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. SUM(Home_Score_FT) AS Home_Score_SUM

is dus de
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. IF(a.homereamID=?


bepalend of er uit of thuis gespeeld is.
Offline Gerard - 18/11/2012 20:07 (laatste wijziging 18/11/2012 21:12)
Avatar van Gerard Ouwe rakker Subqueries zijn aardig langzaam, maar als je het puur SQL wilt houden dan kan het onderstaande het resultaat leveren wat je zoekt. Maar ik zou je aanraden om gewoon de wedstrijden op te halen en dan in PHP te bepalen of het gewonnen/gelijk/verloren is.

sql code - Bekijk de code zonder highlighting - Klap code in 
  1. SELECT DISTINCT 'home' AS `location`, 
  2. 	(SELECT COUNT(*) 
  3. 	 FROM af_games AS agw 
  4. 	 WHERE agw.Home_Team_ID = ag.Home_Team_ID 
  5. 	 AND   agw.Home_Score_FT > agw.Away_Score_FT) AS 'won',
  6. 	(SELECT COUNT(*)
  7. 	 FROM af_games AS agt
  8. 	 WHERE agt.Home_Team_ID = ag.Home_Team_ID
  9. 	 AND   agt.Home_Score_FT = agt.Away_Score_FT) AS 'tie',
  10. 	(SELECT COUNT(*)
  11. 	 FROM af_games AS agl
  12. 	 WHERE agl.Home_Team_ID = ag.Home_Team_ID
  13. 	 AND   agl.Home_Score_FT < agl.Away_Score_FT) AS 'loss'
  14. FROM af_games AS ag
  15. WHERE ag.Season_ID = 99
  16. AND   ag.Home_Team_ID = 3
  17. AND   ag.Game_Played = 'Y'
  18. UNION
  19. SELECT DISTINCT 'away' AS `location`, 
  20. 	(SELECT COUNT(*) 
  21. 	 FROM af_games AS agw 
  22. 	 WHERE agw.Away_Team_ID = ag.Away_Team_ID 
  23. 	 AND   agw.Away_Score_FT > agw.Home_Score_FT) AS 'won',
  24. 	(SELECT COUNT(*)
  25. 	 FROM af_games AS agt
  26. 	 WHERE agt.Away_Team_ID = ag.Away_Team_ID
  27. 	 AND   agt.Away_Score_FT = agt.Home_Score_FT) AS 'tie',
  28. 	(SELECT COUNT(*)
  29. 	 FROM af_games AS agl
  30. 	 WHERE agl.Away_Team_ID = ag.Away_Team_ID
  31. 	 AND   agl.Away_Score_FT < agl.Home_Score_FT) AS 'loss'
  32. FROM af_games AS ag
  33. WHERE ag.Season_ID = 99
  34. AND   ag.Away_Team_ID = 3
  35. AND   ag.Game_Played = 'Y';


Mocht je een ranking willen hebben voor een heel seizoen, dan zou je iets in de trent van onderstaand kunnen doen:
sql code - Bekijk de code zonder highlighting - Klap code in 
  1. SELECT DISTINCT ag.Home_Team_ID,
  2. 	(SELECT COUNT(*) 
  3. 	 FROM af_games AS agw 
  4. 	 WHERE agw.Home_Team_ID = ag.Home_Team_ID 
  5. 	 AND   agw.Home_Score_FT > agw.Away_Score_FT) AS 'home_won',
  6. 	(SELECT COUNT(*)
  7. 	 FROM af_games AS agt
  8. 	 WHERE agt.Home_Team_ID = ag.Home_Team_ID
  9. 	 AND   agt.Home_Score_FT = agt.Away_Score_FT) AS 'home_tie',
  10. 	(SELECT COUNT(*)
  11. 	 FROM af_games AS agl
  12. 	 WHERE agl.Home_Team_ID = ag.Home_Team_ID
  13. 	 AND   agl.Home_Score_FT < agl.Away_Score_FT) AS 'home_loss',
  14. 	(SELECT COUNT(*) 
  15. 	 FROM af_games AS agw 
  16. 	 WHERE agw.Away_Team_ID = ag.Home_Team_ID 
  17. 	 AND   agw.Away_Score_FT > agw.Home_Score_FT) AS 'away_won',
  18. 	(SELECT COUNT(*)
  19. 	 FROM af_games AS agt
  20. 	 WHERE agt.Away_Team_ID = ag.Home_Team_ID
  21. 	 AND   agt.Away_Score_FT = agt.Home_Score_FT) AS 'away_tie',
  22. 	(SELECT COUNT(*)
  23. 	 FROM af_games AS agl
  24. 	 WHERE agl.Away_Team_ID = ag.Home_Team_ID
  25. 	 AND   agl.Away_Score_FT < agl.Home_Score_FT) AS 'away_loss'
  26. FROM af_games AS ag
  27. WHERE ag.Season_ID = 99
  28. AND   ag.Game_Played = 'Y'


Mocht je die gelijk al in de juiste volgorde willen hebben, dan kan dat ook. Maar in dat geval wordt het geheel wel heel erg langzaam.

sql code - Bekijk de code zonder highlighting - Klap code in 
  1. SELECT DISTINCT ag.Home_Team_ID,
  2. 	(SELECT COUNT(*) 
  3. 	 FROM af_games AS agw 
  4. 	 WHERE agw.Home_Team_ID = ag.Home_Team_ID 
  5. 	 AND   agw.Home_Score_FT > agw.Away_Score_FT) AS `home_won`,
  6. 	(SELECT COUNT(*)
  7. 	 FROM af_games AS agt
  8. 	 WHERE agt.Home_Team_ID = ag.Home_Team_ID
  9. 	 AND   agt.Home_Score_FT = agt.Away_Score_FT) AS `home_tie`,
  10. 	(SELECT COUNT(*)
  11. 	 FROM af_games AS agl
  12. 	 WHERE agl.Home_Team_ID = ag.Home_Team_ID
  13. 	 AND   agl.Home_Score_FT < agl.Away_Score_FT) AS `home_loss`,
  14. 	(SELECT COUNT(*) 
  15. 	 FROM af_games AS agw 
  16. 	 WHERE agw.Away_Team_ID = ag.Home_Team_ID 
  17. 	 AND   agw.Away_Score_FT > agw.Home_Score_FT) AS `away_won`,
  18. 	(SELECT COUNT(*)
  19. 	 FROM af_games AS agt
  20. 	 WHERE agt.Away_Team_ID = ag.Home_Team_ID
  21. 	 AND   agt.Away_Score_FT = agt.Home_Score_FT) AS `away_tie`,
  22. 	(SELECT COUNT(*)
  23. 	 FROM af_games AS agl
  24. 	 WHERE agl.Away_Team_ID = ag.Home_Team_ID
  25. 	 AND   agl.Away_Score_FT < agl.Home_Score_FT) AS `away_loss`
  26. FROM af_games AS ag
  27. WHERE ag.Season_ID = 99
  28. AND   ag.Game_Played = 'Y'
  29. ORDER BY ((`home_won` * 3) + `home_tie` + (`away_won` * 3) + `away_tie`) DESC


Mocht je dit scoreboard altijd willen gebruiken, overweeg dan ook eens om er gewoon een VIEW van te maken. Op die manier heb je de gegevens altijd beschikbaar in een handzaam formaat. Maar let echt op de performance, want die 6 subqueries van me zijn dodelijk. Die worden per wedstrijd getriggered. Ik stel dan ook voor dat je uitgaat vanuit de teams die mee doen in een seizoen, maar om daar een goede query voor te schrijven heb ik wat meer database info nodig.

sql code - Bekijk de code zonder highlighting - Klap code in 
  1. CREATE OR REPLACE
  2.  ALGORITHM = UNDEFINED
  3.  VIEW `ag_season_scoreboard`
  4.  (Season_ID,Team_ID,home_won,home_tie,home_loss,away_won,away_tie,away_loss)
  5.  AS SELECT DISTINCT ag.Season_ID, ag.Home_Team_ID,
  6. 	(SELECT COUNT(*) 
  7. 	 FROM af_games AS agw 
  8. 	 WHERE agw.Home_Team_ID = ag.Home_Team_ID 
  9. 	 AND   agw.Home_Score_FT > agw.Away_Score_FT) AS `home_won`,
  10. 	(SELECT COUNT(*)
  11. 	 FROM af_games AS agt
  12. 	 WHERE agt.Home_Team_ID = ag.Home_Team_ID
  13. 	 AND   agt.Home_Score_FT = agt.Away_Score_FT) AS `home_tie`,
  14. 	(SELECT COUNT(*)
  15. 	 FROM af_games AS agl
  16. 	 WHERE agl.Home_Team_ID = ag.Home_Team_ID
  17. 	 AND   agl.Home_Score_FT < agl.Away_Score_FT) AS `home_loss`,
  18. 	(SELECT COUNT(*) 
  19. 	 FROM af_games AS agw 
  20. 	 WHERE agw.Away_Team_ID = ag.Home_Team_ID 
  21. 	 AND   agw.Away_Score_FT > agw.Home_Score_FT) AS `away_won`,
  22. 	(SELECT COUNT(*)
  23. 	 FROM af_games AS agt
  24. 	 WHERE agt.Away_Team_ID = ag.Home_Team_ID
  25. 	 AND   agt.Away_Score_FT = agt.Home_Score_FT) AS `away_tie`,
  26. 	(SELECT COUNT(*)
  27. 	 FROM af_games AS agl
  28. 	 WHERE agl.Away_Team_ID = ag.Home_Team_ID
  29. 	 AND   agl.Away_Score_FT < agl.Home_Score_FT) AS `away_loss`
  30. FROM af_games AS ag
  31. WHERE ag.Game_Played = 'Y'
Offline markla - 18/11/2012 22:45
Avatar van markla PHP interesse TOP!! bedankt voor het ZEER uitgebreide antwoord een de voorbeelden echt onwijs bedankt

hiervan kan ik nog veel leren en ik ga hiermee aan de slag.

thanks!!
Offline Gerard - 18/11/2012 22:55 (laatste wijziging 18/11/2012 23:00)
Avatar van Gerard Ouwe rakker Heb je misschien voor mij ook nog de tabel die hoort bij de Teams? Ik kan dan eens kijken of ik die queries niet een stuk sneller kan krijgen. Misschien dat we met wat indexes nog wat kunnen bereiken. (binnen phpMyAdmin gewoon even naar tabje Export op Database niveau)

Ik heb er nu in ieder geval met 2 Indexes op Away_Team_ID en Home_Team_ID een goede 0,1 seconde eraf gesloopt (0,4 Sec ipv 0,5 Sec). Dat moet beter kunnen 
Offline markla - 19/11/2012 22:35
Avatar van markla PHP interesse Gerard,
Ik denk dat ik nu heb wat ik wilde hebben. aangezien de gegevens van voorgaande seizoen nooit meer zullen veranderen is dit voor het doel dat is ze gebruik snel genoeg.

Met de query wil statisch Pie-charts genereren.

Daarvoor gebruik ik phpgraphlib nu ben ik een NOOB, maar hoe kan ik kan ik een array vullen met variabelen?
even in gekort heb ik dit maar dat werk niet :-(
php code - Bekijk de code zonder highlighting - Klap code in 
  1. while($record = mysql_fetch_array($QUERY_wdl))
  2. {
  3. $data = array("Winst" => $record[home_win], "gelijk" => $record[home_draw],"verlies" => $record[home_los] );
Offline vinTage - 19/11/2012 22:42
Avatar van vinTage Nieuw lid
php code - Bekijk de code zonder highlighting - Klap code in 
  1. $data = array();
  2. while($record = mysql_fetch_array($QUERY_wdl))
  3. {
  4. $data[] = array("Winst" => $record[home_win], "gelijk" => $record[home_draw],"verlies" => $record[home_los] );
Offline markla - 19/11/2012 23:15
Avatar van markla PHP interesse dat is hem, thanks :-)
Offline Gerard - 20/11/2012 21:03 (laatste wijziging 20/11/2012 21:04)
Avatar van Gerard Ouwe rakker Doe mij dan toch nog maar even dat tabelletje 

Gewoon even een Export van phpMyAdmin plaatsen op plaatscode.be
Gesponsorde links
Je moet ingelogd zijn om een reactie te kunnen posten.
Actieve forumberichten
© 2002-2024 Sitemasters.be - Regels - Laadtijd: 0.215s