SQLQuery COUNT number of games per team

Question

I have 4 tables:

Teams

codTeam: 1
year: 1995
codYears: 1
codType: 1
name: FCP
points: 3

codTeam: 2
year: 1990
codYears: 1
codType: 1
name: SLB
points: 3

codTeam: 3
year: 1995
codYears: 3
codType: 2
name: BCP
points: 0

Trainers (People who train a team)

codTrainer: 1
name: Peter
street: Ghost street
cellphone: 252666337
birthdayDate: 1995-02-01
BI: 11111111
number: 121212121

codTrainer: 1
name: Pan
street: Ghost street Remade
cellphone: 253999666
birthdayDate: 1995-01-01
BI: 22222222
number: 212121212

TeamsTrainers (In which team is someone training)

codTeamTrainer: 1
codTeam: 1
codTrainer: 2
dataInicio: 1998-05-05

codTeamTrainer: 2
codTeam: 2
codTrainer: 2
dataInicio: 1998-06-07

codTeamTrainer: 3
codTeam: 2
codTrainer: 1
dataInicio: 1999-09-09

Games

codGame: 1
date: 2015-02-12 13:00:00
codTeamHome: 1
codTeamAgainst: 2
goalsHome: 3
goalsAgainst: 2
codTypeGame: 1

codGame: 2
date: 2015-02-12 15:00:00
codTeamHome: 2
codTeamAgainst: 1
goalsHome: 1
goalsAgainst: 2
codTypeGame: 3

So basically I want to:

Get the table Games and show:

I have the following code for that in SQLQuery:

SELECT Teams.name, Trainers.name, Games.goalsHome,
       Games.goalsAgainst, Teams.points, COUNT(*)
FROM Teams, Trainers, Games, TeamsTrainers
WHERE Games.codTeamHome = Teams.codTeam AND 
      TeamsTrainers.codTeam = Teams.codTeam AND 
      TeamsTrainers.codTrainer = Trainers.codTrainer
GROUP BY Teams.name, Trainers.name, Games.goalsHome, 
         Games.goalsAgainst, Teams.points

(May have some errors as I translated)

Yet, the COUNT only shows 1 (Probably because on the WHERE it has "teamHome" so it only counts 1), yet, if it's because of that, how do I fix it?

Result:

FCP | Pan | 3 | 2 | 3 | 1 (Count)
SLB | Peter | 1 | 2 | 3 | 1 (Count)
SLB | Pan | 1 | 2 | 3 | 1 (Count)

It should be 2 for each one on the Count

Any idea?

What's database: SQL Server, PostgreSQL, MySQL? – Maciej Los Feb 12 '15 at 22:14 — Maciej Los, Feb 12 '15 at 22:14

Maciej Los · Accepted Answer · 2015-02-12T23:01:27.403

1

The reason you get wrong result is of wrong joing data type. You should use repsectivelly: left, right or inner join instead of joing data via using where clause. Your data model provides 1 to N relationship, so you should use specific type of join.

See: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

EDIT

SELECT Te.name, Tr.name, Ga.goalsHome, Ga.goalsAgainst, Te.points,
       (SELECT COUNT(*)
        FROM Games 
        WHERE codTeamHome = Te.codTeam OR codTeamAgainst = Te.codTeam)
        AS CountOfGames
FROM TeamsTrainers AS Tt
    LEFT JOIN Teams AS Te ON Tt.codTeam = Te.codTeam
    LEFT JOIN Trainers AS Tr ON Tt.codTrainer = Tr.codTrainer
    LEFT JOIN Games AS Ga ON  Ga.codTeamHome = Te.codTeam

SQL Fiddle

edited Feb 12 '15 at 23:01

answered Feb 12 '15 at 22:21

Maciej Los

8,468
1
20
35

Don't really understand how to use it – Ran Feb 12 '15 at 22:32
Which part? What do you want to count? – Maciej Los Feb 12 '15 at 22:34
The total amount of games a team has. (In this case, they both play twice, so the count should be 2, and not 1) – Ran Feb 12 '15 at 22:35
Ok, i'll try to create SQL Fiddle for you... Wait. – Maciej Los Feb 12 '15 at 22:37
Added the select count without the rest and worked, as I don't really understand the rest as I never saw it in my life. Sorry for making you lose some time on that part, but it helped me in the end. Thank you very much. – Ran Feb 12 '15 at 23:19

score 0 · Answer 2 · answered Feb 12 '15 at 22:10

0

You can change your WHERE clause by saying

[what you have] OR (Games.codTeamAgainst = Teams.codTeam AND ...)

However, this probably causes other problems because you probably care about whether a particular team scores the goals, not whether the home team scores the goals in games that team plays on either side.

You might not notice the other problems for a while because your GROUP BY clause is probably pretty far from what you want, and you might want to be selecting aggregate functions for a much simpler grouping.

answered Feb 12 '15 at 22:10

Douglas Zare

3,296
1
14
21

That only spammed even more the games and didn't change the count. :/ – Ran Feb 12 '15 at 22:14
@Ran: Do you understand what that GROUP BY condition does? See http://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns. As I pointed out in my answer, you are probably making way too many groups. You are only pooling together games with exactly the same score, so you aren't grouping the game where the home team won 3-2 with the game where the home team lost 1-2. That's why you are getting a count of 1 game with a score of 3-2. – Douglas Zare Feb 12 '15 at 23:37

SQLQuery COUNT number of games per team

2 Answers2