SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist vhogarth's Avatar
    Join Date
    Nov 2003
    Location
    Taxachussets
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •