help me sort records please...

# Thread: help me sort records please...

1. Junior Member
Join Date
Dec 1969
Posts
19

## help me sort records please...

I have a database with these columns:<BR>game_id, player_id, hr, hits, rbi, runs<BR><BR>on my page, i have a table that displays the total stats of each player (i add all the hr, hits, rbi, and runs where player_id=player_id to get the players total stats).<BR><BR>how can i now sort the records by total stats?<BR><BR>tia,<BR>Jon<BR>

2. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: help me sort records please...

Ok, I&#039;m a baseball nitwit, but what are total stats? Which field are added to get the total stats?<BR><BR>SELECT Player_ID, SUM(HR) as TotalHR, SUM(Hits) as TotalHits, SUM(rbi) as TotalRBI, SUM(runs) as TotalRuns FROM playerTable<BR>GROUP By PlayerID<BR>ORDER BY 1 <BR><BR>would order by total Homeruns ascending. But I think you want some aggregate on multiple columns, am I right?

3. Senior Member
Join Date
Dec 1969
Posts
1,765

## Welp, start by

sharing how total stats are determined. If you consider rbi&#039;s as what you want, and you&#039;d like players listed by RBI&#039;s from highest to lowest, then do an <BR><BR>ORDER BY RBI DESC

4. Junior Member
Join Date
Dec 1969
Posts
19

## RE: help me sort records please...

OK, here&#039;s the thing...<BR>i don&#039;t have a column for avg, i do the calculation in a script.<BR>how do i sort by avg?

5. Senior Member
Join Date
Dec 1969
Posts
7,686

## In the SCRIPT??

so you loop through the records? Bad Idea, just do it in the db as I showed. You can use AVG(theField) where I used SUM(theField).<BR><BR>Be sure so assign an alias to the SUM(field) as TotalField (where &#039;as totalField&#039; is the alias) if you want to retrieve the field by name and not by number in the recordset. (you would have to do RS("totalFIeld") in this case)

6. Senior Member
Join Date
Dec 1969
Posts
2,930

## RE: Welp, start by

haha "Welp". i got yelled at by using the word "Welp" before. it brings us back to the Americans making up their own words post.<BR><BR>http://www.dictionary.com/search?q=Kludge

7. Senior Member
Join Date
Dec 1969
Posts
1,765

## Its that whole freedom of speech thing.

I think making up words was one of the ten commandments<BR><BR>next to thou shalt not murder, you&#039;ll see.....Thy american shalt make up words!<BR>

8. Junior Member
Join Date
Dec 1969
Posts
19

## RE: In the SCRIPT??

i&#039;ve got an access database and avg is not one of my fields. are you saying that it should be a field? if so, how do i make the calculation in access?

9. Senior Member
Join Date
Dec 1969
Posts
7,686

## AVG is a function in access

SELECT AVG(HR) as AverageHomeRun FROM theTable <BR><BR>returns the Average homeruns for all players<BR><BR>now when you so<BR><BR>SELECT PlayerID, AVG(HR) as AverageHomeRun FROM theTable<BR>GROUP BY PlayerID<BR><BR>returns the average homeruns for every player.

10. Junior Member
Join Date
Dec 1969
Posts
19

## RE: AVG is a function in access

oh...but that&#039;s not what i want to do...<BR>totalAVG = totalHITS / totalAB<BR>(total hits divided by total times at-bat...this gives the players batting average)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•