[Mysql] Count in een join

[Thomasje]

Ik heb al een topic geopend eerder over het zelfde ding. Alleen is dat probleem al opgelost. Nu zit ik met het volgende probleem. Ik probeer een telling te maken van hoeveel wedstrijden elk team gespeeld heeft. Ik kom hier niet aan uit.

Telling:

PHP:

count(home_game.home_score)+count(away_game.away_score) AS games

Dat werkt dus niet. Krijg ik een verkeerd getal. Wat doe ik fout?


Query zonder telling:

PHP:

SELECT team.name, coalesce( SUM( home_game.home_score ) , 0 ) + coalesce( SUM( away_game.away_score ) , 0 ) AS runs_scored, coalesce( SUM( home_game.away_score ) , 0 ) + coalesce( SUM( away_game.home_score ) , 0 ) AS runs_lost, (
coalesce( SUM( home_game.home_score ) , 0 ) + coalesce( SUM( away_game.away_score ) , 0 )
) - ( coalesce( SUM( home_game.away_score ) , 0 ) + coalesce( SUM( away_game.home_score ) , 0 ) ) AS saldo, count( home_game.home_score ) + count( home_game.away_score ) AS test
FROM `team`
LEFT JOIN game AS home_game ON ( home_game.home_team = team.id )
LEFT JOIN game AS away_game ON ( away_game.away_team = team.id )
GROUP BY team.id

[aex351]

Volgens mij kan je niet in een select + gebruiken en count tegelijkertijd. Je wilt iets bij elkaar optellen zonder dat mysql het op dat punt nog weet.

- edit : volgens mij klopt wat ik hierboven zeg niet

[ Voor 18% gewijzigd door aex351 op 23-02-2006 14:42 ]

[Verwijderd]

Probeer het eens tussen haken te zetten bij voorbeeld :
select (count(a) + count(b)) as bla from foo

[Thomasje]

Verwijderd schreef op donderdag 23 februari 2006 @ 14:45:
Probeer het eens tussen haken te zetten bij voorbeeld :
select (count(a) + count(b)) as bla from foo

Dan blijft het resultaat het zelfde. En aantal gescoorde punten en verloren punten werkt wel gewoon.

[Dido]

Count geeft je aantal records

Dus jij wilt 2*count(*). Of niet?

Ik denk dat jij sum(a+b) wilt hebben. Let wel ik weet niet of sum(a) + sum(b) ook werkt, maar sum(a+b) zou exact hetzelfde op moeten leveren.

[Thomasje]

Dido schreef op donderdag 23 februari 2006 @ 14:58:
Count geeft je aantal records

Dus jij wilt 2*count(*). Of niet?

Ik denk dat jij sum(a+b) wilt hebben. Let wel ik weet niet of sum(a) + sum(b) ook werkt, maar sum(a+b) zou exact hetzelfde op moeten leveren.

Sum telt toch de waardes van de records op. Ik moet aantal gespeelde wedstrijden hebben.

[Dido]

Ah!
Maar je krijgt precies 1 record per wedstrijd terug, of niet (die je vervolgens grouped, natuurlijk)?

Dan zou count(*) toch gewoon moeten werken?

[ Voor 16% gewijzigd door Dido op 23-02-2006 15:06 ]

[Thomasje]

Dido schreef op donderdag 23 februari 2006 @ 15:05:
Ah!
Maar je krijgt precies 1 record per wedstrijd terug, of niet (die je vervolgens grouped, natuurlijk)?

Dan zou count(*) toch gewoon moeten werken?

ik heb gegrouped op team.id. Maar krijg verkeerde waardes terug.

count(*) werkt dus niet

[Jaap-Jan]

Zeg dan eens wat je krijgt en wat het zou moeten zijn, misschien dat dat mensen op ideeën brengt.
Dus: wat geeft je originele query, wat geeft count(*) en wat zou het moeten zijn .

[Thomasje]

Ik heb ff de benodigde tabellen geexporteerd. Zou je er eens naar kunnen kijken?

PHP:

CREATE TABLE `club` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

-- 
-- Gegevens worden uitgevoerd voor tabel `club`
-- 


INSERT INTO `club` VALUES (1, 'Wizards of Boz');
INSERT INTO `club` VALUES (2, 'Tilburg');
INSERT INTO `club` VALUES (3, 'Mulo');
INSERT INTO `club` VALUES (4, 'Arhnem Rhinos');





CREATE TABLE `team` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `club_id` int(10) unsigned NOT NULL default '0',
  `season_id` int(10) unsigned NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `club_id` (`club_id`),
  KEY `season_id` (`season_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Gegevens worden uitgevoerd voor tabel `team`
-- 

INSERT INTO `team` VALUES (1, 1, 1, 'Heren 1');
INSERT INTO `team` VALUES (2, 2, 1, 'Tilburg');
INSERT INTO `team` VALUES (3, 3, 1, 'Mulo');
INSERT INTO `team` VALUES (4, 4, 1, 'Arhnem Rhinos');


CREATE TABLE `game` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `game_nr` varchar(10) NOT NULL default '',
  `game_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `home_team` int(10) unsigned NOT NULL default '0',
  `away_team` int(10) unsigned NOT NULL default '0',
  `home_score` int(3) unsigned default NULL,
  `away_score` int(3) unsigned default NULL,
  `game_status` enum('open','played') NOT NULL default 'open',
  PRIMARY KEY  (`id`),
  KEY `home_team` (`home_team`),
  KEY `away_team` (`away_team`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

-- 
-- Gegevens worden uitgevoerd voor tabel `game`
-- 

INSERT INTO `game` VALUES (1, '', '2006-02-12 00:00:00', 1, 2, 4, 3, 'played');
INSERT INTO `game` VALUES (2, '', '2006-02-14 00:00:00', 2, 1, 2, 13, 'played');
INSERT INTO `game` VALUES (3, '', '0000-00-00 00:00:00', 1, 3, 2, 1, 'played');
INSERT INTO `game` VALUES (4, '', '0000-00-00 00:00:00', 2, 3, 1, 1, 'played');
INSERT INTO `game` VALUES (5, '', '0000-00-00 00:00:00', 4, 3, 10, 3, 'played');

Met de volgende query

PHP:

SELECT team.name, coalesce( SUM( home_game.home_score ) , 0 ) + coalesce( SUM( away_game.away_score ) , 0 ) AS runs_scored, coalesce( SUM( home_game.away_score ) , 0 ) + coalesce( SUM( away_game.home_score ) , 0 ) AS runs_lost, (
coalesce( SUM( home_game.home_score ) , 0 ) + coalesce( SUM( away_game.away_score ) , 0 )
) - ( coalesce( SUM( home_game.away_score ) , 0 ) + coalesce( SUM( away_game.home_score ) , 0 ) ) AS saldo, count( * ) AS games
FROM `team`
LEFT JOIN game AS home_game ON ( home_game.home_team = team.id )
LEFT JOIN game AS away_game ON ( away_game.away_team = team.id )
GROUP BY team.id

[ Voor 8% gewijzigd door Thomasje op 23-02-2006 15:16 ]

[Thomasje]

Japie_17 schreef op donderdag 23 februari 2006 @ 15:11:
Zeg dan eens wat je krijgt en wat het zou moeten zijn, misschien dat dat mensen op ideeën brengt.
Dus: wat geeft je originele query, wat geeft count(*) en wat zou het moeten zijn .

Er zijn 5 wedstrijden gespeeld dus de games bij elkaar opgeteld zou 10 moeten zijn. Het is op een of andere manier nu 8.

[Dido]

Wat krijg je voor resultaat als je die group by even weglaat?

(Sowieso een beetje vaag dat je team.naam select en op team.id grouped, maar dat zou moeten kunnen).

[Thomasje]

Dido schreef op donderdag 23 februari 2006 @ 15:38:
Wat krijg je voor resultaat als je die group by even weglaat?

(Sowieso een beetje vaag dat je team.naam select en op team.id grouped, maar dat zou moeten kunnen).

Als ik het weglaat krijg ik het volgende.

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause


Ik select op team.naam omdat ik even wil zien welk team het is en geen cijfer.

[Dido]

Sorry, da's logisch.

Even wat zitten vogelen... die join is wat raar: je doet eigenlijk

select blaat from (Tabel1 join tabel2) join tabel3. Ik denk dat je dan deze records krijgt:
(team, homeH, HomeA, AwayH, AwayA)

1 1 2 2 1
1 1 3 2 1
2 2 1 1 2
2 2 3 1 2
3 *null *null 1 3
3 *null *null 2 3
3 *null *null 4 3
4 4 3 *null *null

Dat zijn er 8...
Ook betwijfel ik dus of je andere resultaten echt kloppen, aangezien je games 1 en 2 in ieder geval dubbel terugkrijgt.

[ Voor 15% gewijzigd door Dido op 23-02-2006 15:50 ]

[Thomasje]

Dido schreef op donderdag 23 februari 2006 @ 15:47:
Sorry, da's logisch.

Even wat zitten vogelen... die join is wat raar: je doet eigenlijk

select blaat from (Tabel1 join tabel2) join tabel3. Ik denk dat je dan deze records krijgt:
(team, homeH, HomeA, AwayH, AwayA)

1 1 2 2 1
1 1 3 2 1
2 2 1 1 2
2 2 3 1 2
3 *null *null 1 3
3 *null *null 2 3
3 *null *null 4 3
4 4 3 *null *null

Dat zijn er 8...
Ook betwijfel ik dus of je andere resultaten echt kloppen, aangezien je games 1 en 2 in ieder geval dubbel terugkrijgt.

Je hebt gelijk, klopt helemaal geen kont van. Het moet toch mogelijk zijn!

[Shezzie]

Ik heb je MySQL hier even vertaald naar MS SQL2k. Probeer eens het volgende:

SELECT aTeam, SUM(Games) as Played FROM( SELECT iHome_Team as aTeam, COUNT(iId) as Games FROM Game Group BY iHome_Team UNION ALL SELECT iAway_Team as aTeam, COUNT(iId) as Games FROM Game Group BY iAway_Team ) as GamesPlayed Group By aTeam

Dat levert hier hetvolgende op:

aTeam - Played
1- 3
2 -3
3 -3
4 -1

Geen idee of MySql UNION ALL kent, maar ik denk dat je het principe anders wel snapt

[Thomasje]

Shezzie schreef op donderdag 23 februari 2006 @ 16:32:
Ik heb je MySQL hier even vertaald naar MS SQL2k. Probeer eens het volgende:

SELECT aTeam, SUM(Games) as Played FROM( SELECT iHome_Team as aTeam, COUNT(iId) as Games FROM Game Group BY iHome_Team UNION ALL SELECT iAway_Team as aTeam, COUNT(iId) as Games FROM Game Group BY iAway_Team ) as GamesPlayed Group By aTeam

Dat levert hier hetvolgende op:

aTeam - Played
1- 3
2 -3
3 -3
4 -1

Geen idee of MySql UNION ALL kent, maar ik denk dat je het principe anders wel snapt

Ja mysql kent union. Krijg nu ook de zelfde waarde. Nu even proberen of ik de andere waardes er nu ook in krijg.

[Shezzie]

Ik ben zo naar huis, hier de oplossing in case je hem nog nodig hebt:

SELECT Club.szName, SUM(Games) as Played, SUM(sc) as runs_scored, SUM(ls) as runs_lost, SUM(sc) - SUM(ls) as saldo FROM( SELECT iHome_Team as aTeam, COUNT(iId) as Games, SUM(iHome_score) as sc, SUM(iAway_score) as ls FROM Game Group BY iHome_Team UNION ALL SELECT iAway_Team as aTeam, COUNT(iId) as Games, SUM(iAway_score) as sc, SUM(iHome_score) as ls FROM Game Group BY iAway_Team ) as j1 JOIN Club ON j1.aTeam = Club.iId GROUP BY Club.szName ORDER BY saldo DESC

levert het volgende op:

Name	Played	scored	lost	saldo
Wizards of Boz	3	19	6	13
Arhnem Rhinos	1	10	3	7
Mulo	3	5	13	-8
Tilburg	3	6	18	-12

Good luck,

Shez.

[Thomasje]

Shezzie schreef op donderdag 23 februari 2006 @ 17:48:
Ik ben zo naar huis, hier de oplossing in case je hem nog nodig hebt:

SELECT Club.szName, SUM(Games) as Played, SUM(sc) as runs_scored, SUM(ls) as runs_lost, SUM(sc) - SUM(ls) as saldo FROM( SELECT iHome_Team as aTeam, COUNT(iId) as Games, SUM(iHome_score) as sc, SUM(iAway_score) as ls FROM Game Group BY iHome_Team UNION ALL SELECT iAway_Team as aTeam, COUNT(iId) as Games, SUM(iAway_score) as sc, SUM(iHome_score) as ls FROM Game Group BY iAway_Team ) as j1 JOIN Club ON j1.aTeam = Club.iId GROUP BY Club.szName ORDER BY saldo DESC

levert het volgende op:

Name	Played	scored	lost	saldo
Wizards of Boz	3	19	6	13
Arhnem Rhinos	1	10	3	7
Mulo	3	5	13	-8
Tilburg	3	6	18	-12

Good luck,

Shez.

Thnx Shezzie ik heb er zeker wat aan gehad. Ik de union niet gebruikt want was opzich niet nodig. Dit is mijn query tot nu toe. Hij berekend ook hoeveel punten je hebt. Heb ook hulp van mensen buiten got om gehad.

PHP:

SELECT team.name, count( DISTINCT home_game.id ) + count( DISTINCT away_game.id ) AS games, (
COUNT( DISTINCT won_game.id ) *2 + COUNT( DISTINCT draw_game.id )
) AS points, COUNT( DISTINCT won_game.id ) AS won, COUNT( DISTINCT draw_game.id ) AS draw, COUNT( DISTINCT lost_game.id ) AS lost, coalesce( (

SELECT sum( home_score )
FROM game
WHERE home_team = team.id ) , 0
) + coalesce( (

SELECT sum( away_score )
FROM game
WHERE away_team = team.id ) , 0
) AS runs_won, coalesce( (

SELECT sum( away_score )
FROM game
WHERE home_team = team.id ) , 0
) + coalesce( (

SELECT sum( home_score )
FROM game
WHERE away_team = team.id ) , 0
) AS runs_lost, (
coalesce( (

SELECT sum( home_score )
FROM game
WHERE home_team = team.id ) , 0
) + coalesce( (

SELECT sum( away_score )
FROM game
WHERE away_team = team.id ) , 0
)
) - ( coalesce( (

SELECT sum( away_score )
FROM game
WHERE home_team = team.id ) , 0 ) + coalesce( (

SELECT sum( home_score )
FROM game
WHERE away_team = team.id ) , 0
)
) AS saldo
FROM `team`
LEFT JOIN game AS home_game ON ( home_game.home_team = team.id )
LEFT JOIN game AS away_game ON ( away_game.away_team = team.id )
LEFT JOIN game AS won_game ON ( (
won_game.home_team = team.id
AND won_game.home_score > won_game.away_score
)
OR (
won_game.away_team = team.id
AND won_game.home_score < won_game.away_score
) )
LEFT JOIN game AS draw_game ON ( (
draw_game.home_team = team.id
AND draw_game.home_score = draw_game.away_score
)
OR (
draw_game.away_team = team.id
AND draw_game.home_score = draw_game.away_score
) )
LEFT JOIN game AS lost_game ON ( (
lost_game.home_team = team.id
AND lost_game.home_score < lost_game.away_score
)
OR (
lost_game.away_team = team.id
AND lost_game.home_score > lost_game.away_score
) )
GROUP BY team.id
ORDER BY points DESC