Do I need some sort of self join?
Posted: Mon Mar 10, 2008 4:38 pm
Simple table, 3 columns. lineid corresponds to an individual game. teamid is the team name. ident is a text field that is either a number or a name (for some players I have numbers and for some I have names).
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (1, Rangers, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (1, Rangers, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (1, Rangers, 12)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (2, Celtic, 8 )
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (2, Celtic, Johnson)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (3, Portsmouth, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (3, Portsmouth, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (3, Portsmouth, Johnson)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (4, Newcastle, 7)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (4, Newcastle, Smith)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (5, Rangers, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (6, Newcastle, 7)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (6, Newcastle, 7)
Each record is a goal scored. In this data Rangers beat Celtic 3-2, with Rangers #3 scoring 2 goals. Real data uses names of my local teams and actual goal scoring friends.
What I want is a leading goal scorers table.
For this data it would look like:
Team, Ident, Goals Scored
Rangers 3 3
Newcastle 7 3
Portsmouth 3 2
Rangers 12 1
Celtic 8 1
Celtic Johnson 1
Portsmouth Johnson 1
Newcastle Smith 1
I keep thinking the "goals scored" column should be something like "count of the occurences of the unique combination of teamid and ident sorted descending" but can't figure out how to translate that to SQL.
SELECT ident, count(ident) AS goals_scored
FROM goal_scorers
GROUP BY ident
ORDER BY count(ident) DESC
gives me all goals scored by anybody with an ident of 3 or a name of johnson, but lumps them together across teams so Portmouth 3 is the same as Rangers 3, etc.
It's an Access database. How do I do it?
Thanks.
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (1, Rangers, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (1, Rangers, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (1, Rangers, 12)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (2, Celtic, 8 )
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (2, Celtic, Johnson)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (3, Portsmouth, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (3, Portsmouth, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (3, Portsmouth, Johnson)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (4, Newcastle, 7)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (4, Newcastle, Smith)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (5, Rangers, 3)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (6, Newcastle, 7)
INSERT INTO goal_scorers (lineid, teamid, ident)
VALUES (6, Newcastle, 7)
Each record is a goal scored. In this data Rangers beat Celtic 3-2, with Rangers #3 scoring 2 goals. Real data uses names of my local teams and actual goal scoring friends.
What I want is a leading goal scorers table.
For this data it would look like:
Team, Ident, Goals Scored
Rangers 3 3
Newcastle 7 3
Portsmouth 3 2
Rangers 12 1
Celtic 8 1
Celtic Johnson 1
Portsmouth Johnson 1
Newcastle Smith 1
I keep thinking the "goals scored" column should be something like "count of the occurences of the unique combination of teamid and ident sorted descending" but can't figure out how to translate that to SQL.
SELECT ident, count(ident) AS goals_scored
FROM goal_scorers
GROUP BY ident
ORDER BY count(ident) DESC
gives me all goals scored by anybody with an ident of 3 or a name of johnson, but lumps them together across teams so Portmouth 3 is the same as Rangers 3, etc.
It's an Access database. How do I do it?
Thanks.