LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 01-15-2004, 09:38 PM   #1
oulevon
Member
 
Registered: Feb 2001
Location: Boston, USA
Distribution: Slackware
Posts: 435

Rep: Reputation: 30
SQL Query question


Hi,

I'm not that familiar with sql. Here's my situation. I'm running mysql, and I have a table of every Batter from the 2003 Baseball season. Each record contains playerID, teamID, games, hits etc. The problem is that some players were traded in midseason last year so there are 2 records for those players, one for their stats from team 1 and one from their stats from team 2. Is there a way in SQL to combine two records? All the fields I need to combine would be small integers.

Alternatively I could combine them after extracting them from the database, but I was just wondering if I could do it through SQL. Thanks for any help.
 
Old 01-15-2004, 11:51 PM   #2
lyle_s
Member
 
Registered: Jul 2003
Distribution: Slackware
Posts: 388

Rep: Reputation: 52
You mean the playerID is different even though it's the same player on a different team?

Lyle
 
Old 01-16-2004, 12:17 AM   #3
oulevon
Member
 
Registered: Feb 2001
Location: Boston, USA
Distribution: Slackware
Posts: 435

Original Poster
Rep: Reputation: 30
No I mean the following:

Code:
+-----------+--------+------+------+------+
| playerID  | teamID | G    | H    | SB   |
+-----------+--------+------+------+------+
| sanchal03 | MIL    |   43 |   46 |    8 |
| sanchal03 | DET    |  101 |  114 |   44 |
+-----------+--------+------+------+------+
2 rows in set (0.00 sec)

So the player is the same, and the playerID is the same but there are 2 different entries one for each team. So I want something like the following:

sanchal03 | DET | 144 | 160 | 52 |
 
Old 01-16-2004, 12:28 AM   #4
lyle_s
Member
 
Registered: Jul 2003
Distribution: Slackware
Posts: 388

Rep: Reputation: 52
Oh, okay. Hopefully this will help:

Code:
SELECT playerID, SUM(G), SUM(H), SUM(SB)
FROM tablename
WHERE playerID='sanchal03';
Lyle
 
Old 01-16-2004, 12:41 AM   #5
oulevon
Member
 
Registered: Feb 2001
Location: Boston, USA
Distribution: Slackware
Posts: 435

Original Poster
Rep: Reputation: 30
Thanks for your help, but I'm getting the following error:

Code:
mysql> SELECT playerID, SUM(G), SUM(H), SUM(SB) FROM Batting WHERE playerID='sanchal03';
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

I'm not sure if this will help, but here is how the Table is setup:

Code:
mysql> DESCRIBE Batting;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| playerID | varchar(9)           |      | PRI |         |       |
| yearID   | smallint(4) unsigned |      | PRI | 0       |       |
| stint    | int(2)               |      | PRI | 0       |       |
| teamID   | char(3)              |      | MUL |         |       |
| lgID     | char(2)              |      |     |         |       |
| G        | smallint(3) unsigned | YES  |     | NULL    |       |
| AB       | smallint(3)          |      |     | 0       |       |
| R        | smallint(3) unsigned | YES  |     | NULL    |       |
| H        | smallint(3) unsigned | YES  |     | NULL    |       |
| 2B       | smallint(3) unsigned | YES  |     | NULL    |       |
| 3B       | smallint(3) unsigned | YES  |     | NULL    |       |
| HR       | smallint(3) unsigned |      |     | 0       |       |
| RBI      | smallint(3) unsigned | YES  |     | NULL    |       |
| SB       | smallint(3) unsigned | YES  |     | NULL    |       |
| CS       | smallint(3) unsigned | YES  |     | NULL    |       |
| BB       | smallint(3) unsigned | YES  |     | NULL    |       |
| SO       | smallint(3) unsigned | YES  |     | NULL    |       |
| IBB      | smallint(3) unsigned | YES  |     | NULL    |       |
| HBP      | smallint(3) unsigned | YES  |     | NULL    |       |
| SH       | smallint(3) unsigned | YES  |     | NULL    |       |
| SF       | smallint(3) unsigned | YES  |     | NULL    |       |
| GIDP     | smallint(3) unsigned | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
 
Old 01-16-2004, 01:03 AM   #6
nephilim
Member
 
Registered: Aug 2003
Location: Belgium
Distribution: Debian (server), Kubuntu (desktop)
Posts: 248

Rep: Reputation: 30
I think that should be

Code:
SELECT playerID, SUM(G), SUM(H), SUM(SB)
FROM tablename
WHERE playerID='sanchal03'
GROUP BY playerID;
 
Old 01-16-2004, 01:13 AM   #7
oulevon
Member
 
Registered: Feb 2001
Location: Boston, USA
Distribution: Slackware
Posts: 435

Original Poster
Rep: Reputation: 30
That works great! Thank you both for helping me out.
 
Old 01-16-2004, 01:50 AM   #8
nephilim
Member
 
Registered: Aug 2003
Location: Belgium
Distribution: Debian (server), Kubuntu (desktop)
Posts: 248

Rep: Reputation: 30
No problem, anytime.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL query performance smaida Programming 6 06-08-2005 09:22 AM
SQL query help pls. vickr1z Programming 8 10-18-2004 11:25 PM
Speeding up the script, or the SQL Query? knickers Programming 1 04-13-2004 11:57 AM
Massive SQL Query patpawlowski Programming 7 03-05-2004 04:24 PM
Bizarre SQL select query acid_kewpie Programming 6 01-20-2004 12:47 PM


All times are GMT -5. The time now is 02:23 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration