Results 1 to 2 of 2
Jul 10, 2004, 11:00 #1
- Join Date
- Nov 2003
- 0 Post(s)
- 0 Thread(s)
problem summing enum columns, sql guru please help
I'm trying to do a sum of a couple enum columns, i got the query to execute without any hangups (after failing a few times). Here is what my query looks like:
SELECT SUM( W_L ) AS total_wl, SUM( forefit ) AS total_forefit, teamName FROM scores, teams WHERE teams.TID = '$TID' AND scores.TID = '$TID' GROUP BY teamName
The result that is returned doesnt come out the way i intended it to.
The values for the W_L and forefit colums are either 0 or 1 (0 for loss, 1 for win) (0 no forefit, 1 forefit)
i'm trying to write a script to get the records, so I want to sum the win loss column. Lets say it would return a sum of 3 for the W_L column and 0 for the forefit column with a row count of 7. That would mean 3 wins and 4 losses. But its not totaling up the column right. Can you help me work the kinks out of this. I'd like to perform this opertion on the DB so that the php part would not be as long.
Jul 10, 2004, 17:38 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 3 Thread(s)
from the mysql docs --
"Values from the list of allowable elements in the
column specification are numbered beginning with 1."
so right away you have to declare your columns like this
create table scores ( ...
, w_l enum ('win')
, forf enum ('forfeit')
then when you insert a row, you have to insert an empty string if you want to record a 0
when you do it this way, you can add them up with SUM
however, i normally advise people to avoid using ENUM altogether, because
1. it's not portable to other databases (whereas TINYINT is)
2. the syntax is just too wonky (as demonstrated above)