×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

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

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: Microsoft: Access Queries and JET SQL 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

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

(OP)
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?

  

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

(OP)
"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?



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

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

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close