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|
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
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'
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.