|
| |
User Groups : Forums : SiteExperts : The Server
:  |  MySQL query question...
Ok, here's the deal. I'm working on a home project, and I'm having a bit of a problem.I have a query that gets points for, points against, and points differential.This part of the query works fine, because these are calculated by my C#, and stored in the DB.The problem is, I'm trying to get a percentage for the points differential, using something akin to this:
SELECT T.TEAM_NAME, S.POINTS_FOR, S.POINTS_ALLOWED, S.POINTS_DIFFERENTIAL,(S.POINTS_DIFFERENTIAL / MAX(S.POINTS_DIFFERENTIAL)) AS PDFROM SCORES SINNER JOIN TEAMS TON S.TEAM_ID = T.IDGROUP BY T.TEAM_NAME;
The results look something like this:
| Team Name | Points Scored | Points Allowed | Points Difference | PD | | Team A | 10 | 14 | -4 | 1.0000 | | Team B | 28 | 10 | 18 | 1.0000 | | Team C | 35 | 27 | 8 | 1.0000 | | Team D | 12 | 10 | 2 | 1.0000 | | Team E | 14 | 0 | 14 | 1.0000 |
The problem with this is the math in the last column is way wrong. It should not be 1.0000 in each column. The last column should have the following values:
-.2222, 1.0000, .4444, .1111, .7777
I know I'm missing something simple. But what? I'll probably kick myself when I find out what it is...Started By Monte on Jul 27, 2011 at 8:05:10 AM | | | 6 Response(s) | Reply |
| View All Replies | | | Monte on Jul 27, 2011 at 11:40:11 AM Got it, but it brings up another question.
Here's the query (albeit still probably pretty inefficient):
SELECT TEAM_NAME, POINTS_PCT, POINTS_DIFFERENTIAL,
(POINTS_DIFFERENTIAL / A) AS POINTS_DIFF_PCT,
((POINTS_PCT + (POINTS_DIFFERENTIAL / A)) / 2) AS RESULT
FROM
(
SELECT T.TEAM_NAME, POINTS_PCT, POINTS_DIFFERENTIAL,
(SELECT MAX(POINTS_DIFFERENTIAL) A FROM SCORES S WHERE WEEK_NUMBER = '0') as A
FROM SCORES S
INNER JOIN TEAMS T ON S.TEAM_ID = T.ID
WHERE WEEK_NUMBER = '0'
) B
GROUP BY TEAM_NAME
ORDER BY RESULT DESC, TEAM_NAME ASC;
The problem (if you want to call it that) I'm having now is that I want to enumerate the rows. In other words, I want the first row to have a 1 in the first column, the second row to have a 2, and so on. How would I do that in MySQL?
| | View All Replies |
To respond to a discussion, you must first logon.
If you are not registered, please register yourself to become a member of the SiteExperts.community.
|
