sqlasp.netsql-serverasp-classicadodb

How to list distinct column after a join and also count?


I have three tables:

team(ID, name)
goal(ID, team_ID, goalType_ID, date)
goalType(ID, name)

As you can see, team_ID is the ID of teams table, and goalType_ID is the ID of goalType table.

For all teams, I want to list the number of different types of goals that ever happened, 0 should appear if none.

We don't need to care about the goals table since we don't need the name of the type of goal so I've gotten to the follow code that only uses the first two tables:

SELECT team.ID, team.name, goal.goaType_ID
FROM team LEFT JOIN goal ON team.ID=goal.team_ID

What this results in is a three-column table of information I want, but I would like to count the number of DISTINCT goalTypes, and GROUP BY team.ID or team.name and keep it three columns and also if the result is null, show 0 (team might not have scored any goals).

The resulting table looks something like this:

team.ID     team.name   goalsType.ID
1           Team_1      1
2           Team_2      2
2           Team_2      2
2           Team_2      2
3           Team_3      4
4           Team_4      null
5           Team_5      null
6           Team_6      1
6           Team_6      2
6           Team_6      4
6           Team_6      3
7           Team_7      5
7           Team_7      4
8           Team_8      null

I have tried a combination of GROUP BY, DISTINCT, and COUNT, but still can't get a result I want.

Am I going about this all wrong?

Edit

Based on Gordon Linoff's answer, I tried doing:

SELECT DISTINCT team.name, COUNT(goal.goalType_ID)
FROM team LEFT JOIN goal ON team.ID=goal.team_ID
GROUP BY team.ID, team.name

and it will give me:

Name        #0
Team_1      1
Team_2      3
Team_3      1
Team_4      0
Team_5      0
Team_6      4
Team_7      1
Team_8      0

If I try to use "DISTINCT team.ID, DISTINCT team.name", it will error out.


Solution

  • Is this what you want?

    SELECT team.ID, team.name, count(distinct goal.goalType_ID) as NumGoalTypes
    FROM team LEFT JOIN
         goal
         ON team.ID = goal.team_ID
    GROUP BY team.ID, team.name;