Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
BigRed1212

BigRed1212

Technical User
Mar 11, 2008
550
0
0
US
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.
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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?

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?



 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
Cross Join in an Insert Into
Replies
1
Views
121
carp
carp
BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
120
carp
carp
BobThornton
  • Locked
  • Question
Count records until a record value changes
Replies
2
Views
157
carp
carp
Bill Doe
  • Locked
  • Question
PDO UPDATE too long to execute
Replies
1
Views
106
mikrom
mikrom
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
105
carp
carp

Part and Inventory Search

Sponsor

Back
Top