I think I need a self join or a subquery to do this, but don't know

[BigRed1212]

I posted this someplace else but this forum looks like it might be a better place.

I have a simple table, 3 columns. Goals scored in soccer games. 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 get numbers and for some I get 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 league 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.

 

[r937]

if it's an Access database, you really can't rely on ANSI SQL to work properly, and therefore you should be posting Access questions into forum701 instead of this one

try this:

SELECT teamid, ident, count(*) AS goals_scored
FROM goal_scorers
GROUP BY teamid, ident
ORDER BY goals_scored DESC



r937.com | rudy.ca

 

[BigRed1212]

Sorry I don't know beans. These are different flavors of SQL for lack of a better term? I'm using an Access data base that I hit with an SQL query in an ASP page. I was just looking to write the SQL to pull the recordset to manipulate and display with ASP/HTML.

You're right. That query doesn't work in Access.

How much trouble will I get in if I just copy this whole thing lock stock and bagel and repost it over in the Access forum thing you ID above? Anyone? Anyone? Bueller?

 

[PHV]

SELECT teamid, ident, Count(*) AS goals_scored
FROM goal_scorers
GROUP BY teamid, ident
ORDER BY 3 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigRed1212]

ORDER BY 3 DESC "

I have no idea what that is or is suppossed to be.

This query works perfectly. I have no idea how it works.

I want to know! What is it doing/what is going on?

 

[r937]

the 3 refers to the ordinal position of the column, i.e. ORDER BY the 3rd column

ORDER BY is the only clause which allows this usage

r937.com | rudy.ca

 

[BigRed1212]

I had no idea one could do such a thing. "Order by 3" is my new buzz phrase.

My life has been made easier. I BELIEVE I will now be able to set up to enter the weeks gamecards into a couple of HTML forms, populate two tables in my database with the info, and then have 4 queries that dynamically show the scores and scorers for each game on the schedule, the overall team standings, and the leading scorers. I have some work to do to make that a reality, however.

I was hard coding all this and I think life will be better now.

Okay. Next task is to learn enough to help somebody else.

Thanks.