|
| |
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
This message has been edited. | | | 6 Response(s) | Reply |
| Earlier Replies | Replies 4 to 6 of 6 | Later Replies | | | Monte on Jul 27, 2011 at 8:41:55 AM (# 4) Well, This seems to be part of the answer:
SELECT T.TEAM_NAME, S.POINTS_FOR, S.POINTS_ALLOWED, S.POINTS_DIFFERENTIAL,
(S.POINTS_DIFFERENTIAL/(SELECT MAX(S.POINTS_DIFFERENTIAL) FROM SCORES S WHERE WEEK_NUMBER = '0')) AS PD
FROM TEAMS T, SCORES S
WHERE T.ID = S.TEAM_ID
AND WEEK_NUMBER = '0'
GROUP BY T.TEAM_NAME;
But it still seems horribly inefficient, especially since week number will be changing and/or not be used.
Is there a better way to write this?
Monte on Jul 27, 2011 at 11:40:11 AM (# 5)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?
pipipeng on Feb 6, 2012 at 12:25:16 AM (# 6)NY Escorts And Asian Escort [url=http://www.edmontonescort.com]Edmonton Escort[/url] Girls Site, We Show All Girls Are 100% Real Photo, New York [url=http://www.edmontonescort.com]Edmonton Escorts[/url] Asian Girls provides sexy Asian escorts in New York.arrange you [url=http://www.edmontonescort.com]Edmonton Asian Escort[/url] to meet with our professional New York Escort. They come from [url=http://www.edmontonescort.com]Edmonton Asian Escorts[/url] many different backgrounds. They are happy and fun to be accompanying with, let our escorts bring you the total enjoyment of life. Call us now! Let our New York Asian Escort show you how much fun you can get just being in New York.
| | Earlier Replies | Replies 4 to 6 of 6 | Later 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.
|
