Basic SQL query question.

March 12, 2007 5:11 PM

SQL help. I'm having a basic SQL problem.

Lets say I have this table:

name/score_pre_trans/transfer/score_post_trans
john/2/yes/1
paul/1/no/1
sam/1/yes/2

"score_pre_trans" is the score a person has gain before the transfer day. "transfer" is whether a player was transferred to another team on transfer day. "score_post_trans" is the score a person has gained after the transfer day.

My question is. How do you write the query if I want to find the person who has score the most point in a single team (pre trans + post trans if a person hasn't been transferred and pre trans only if a person has) Excuse the crappy table. Hope this makes sense.

posted by herbiehancock00 to Computers & Internet (10 answers total)

SELECT CASE WHEN transfer THEN score_pre_trans ELSE score_pre_trans + score_post_trans END FROM players;
posted by PEAK OIL at 5:20 PM on March 12, 2007

(above assumes that this is the result table you wanted.)

name | case
------+------
john | 2
paul | 2
sam | 1
posted by PEAK OIL at 5:21 PM on March 12, 2007

I don't understand your question, because I don't understand the premise of what you're trying to do (what sport? what do you mean transfers?).

However, my initial suggestion is that, when something in SQL / databases isn't obvious, you may need to break things down into a number of separate tables, or rearrange the data somehow.

Table 1

Name   TransferStatus    Score
John     Pre              2
Paul     Pre              1
Sam      Pre              2
John     Post             1
....

Table 2

Name    Transferred
John      Yes
Paul      No
...

If you arrange things like that, and use joins / multiple queries, does it make more sense to you?
posted by Jimbob at 5:25 PM on March 12, 2007

Response by poster: peak oil I tried the query and it didn't work. Is there another way to setup the tables instead so it will require a less complicated query? This is for a SQL 101 kind of class so I haven't learned about then and else functions.
posted by herbiehancock00 at 5:32 PM on March 12, 2007

test=# SELECT name, CASE WHEN transfer THEN score_pre_trans ELSE score_pre_trans + score_post_trans END AS scores FROM players ORDER BY scores DESC;
 name | scores
------+--------
 john |      2
 paul |      2
 sam  |      1

the query works. And yes, there are other ways, but I'm averse to giving further assistance, given that this is apparently for a class, not a personal project.
posted by PEAK OIL at 5:54 PM on March 12, 2007

SELECT NAME, MAX(SCORE)
FROM (SELECT NAME, SCORE_PRE_TRANS+SCORE_POST_TRANS AS SCORE
FROM TABLE WHERE TRANS="NO" UNION SELECT NAME, SCORE_PRE_TRANS FROM TABLE WHERE TRANS="YES");
posted by owhydididoit at 6:00 PM on March 12, 2007

Oh comeon I only get one chance every two weeks to come in here and ask for help in homework. I don't think it's over the top at all.

I tried owhydididoit's query and it doesn't work either. It says I'm trying to execute a query that does not include the specified expression "Name" as part of an aggregate.
posted by willy_dilly at 6:14 PM on March 12, 2007

What kind of SQL are you using for this - access, oracle, mySQL, etc. ?

owhydididoit's query is missing a GROUP BY clause.
try this version:

SELECT NAME, MAX(SCORE)
FROM (SELECT NAME, SCORE_PRE_TRANS+SCORE_POST_TRANS AS SCORE
FROM TABLE WHERE TRANS="NO" UNION SELECT NAME, SCORE_PRE_TRANS FROM TABLE WHERE TRANS="YES")
GROUP BY NAME;
posted by Zetetics at 6:27 PM on March 12, 2007 [1 favorite]

Advice: you're storing aggregate data. Don't. (Your problem requires more granular data, in particular the pre and post transfer scores. And your model simply can't model a player who is transfered more than once.)

Store actual data, that is entities (nouns): a table of players, a table of teams, a table of games.

Then a "lineup" table that holds (foreign keys to) the players on a team for a game. This can also hold each players' score for that game. (Or you could break that out as a scores table; I don't know the questions you're trying to answer/model, so I can't suggest one over the other at this point.)

Then look at SQL's "group by" clause, and use it to "roll up" the lineup table to show players' scores overall, by game, or by team membership. (Or team scores, using the sum() function, or whatever.)

Some more advice: if you're using the case operator, that often means that your model has a deficiency that you're using "case" to patch. (Or you're reformating data for output to a human or another database model.) If you have the freedom to do so, change the model instead.
posted by orthogonality at 6:37 PM on March 12, 2007 [1 favorite]

Zetetics that code works perfectly except for one thing. It displays name and goals per single team for all players instead of the one player with the maximum number of goals.

I know it's bad to ask about school work but I got finals for two other class tomorrow so I need to get this done asap.

Thanks everyone!
posted by willy_dilly at 6:38 PM on March 12, 2007