Results 1 to 6 of 6

Thread: SQL Help

  1. #1
    Join Date
    Jan 2003
    Posts
    32

    Unanswered: SQL Help

    I have this string:

    SELECT *,
    IIF(outcome='win',2,1) AS points FROM Results
    WHERE name='Bam'

    What I need to do now, in this string, is have it total the rows in the points column that I have created in the statement. I tried this:

    SELECT *,
    IIF(outcome='win',2,1) AS points FROM Results
    WHERE name='Bam'
    SUM points AS points2

    But it didn't work. Everyting I tried didn't work. Can someone help me with this please?
    Bam

  2. #2
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191
    Maybe I'm missing something, but it seems to me that

    SELECT SUM(IIF(outcome='win',2,1)) AS points FROM Results WHERE name='Bam"

    should work... Try it.

    Tim
    Tim

  3. #3
    Join Date
    Jan 2003
    Posts
    32
    yep, worked great! Thanks a bunch!
    Bam

  4. #4
    Join Date
    Jan 2003
    Posts
    32
    Thanks to all who have helped me with Sql. I am getting to understand SQL much better. I do have another question though.
    In the string that you gave me, would there be anyway of putting some kind of if statement in there? This is what I want.
    I have this:

    select sum(IIF(outcome='win',2,1)) as points
    FROM Results
    WHERE name='Bam'

    now what I need, is to tell it, if the name Bam appears more than 10 times, not to include it in the total. In other words, a player can only play 10 matches that count, so I want to NOT include anything over 10, or throw it out so to speak. Can this be done?
    Bam

  5. #5
    Join Date
    Apr 2003
    Location
    Evansville, Indiana
    Posts
    76

    Lightbulb

    Not sure if this will work for you...but try something like this.

    select Sum(IIf(sum(IIF(outcome='win',2,1)) > 10, 0, sum(IIF(outcome='win',2,1)))) as points
    FROM Results
    WHERE name='Bam'

    You may not need that final outer Sum()...but this may help.

    Kael

  6. #6
    Join Date
    Oct 2003
    Posts
    16

    now what I need, is to tell it, if the name Bam appears more than 10 times, not to include it in the total. In other words, a player can only play 10 matches that count, so I want to NOT include anything over 10, or throw it out so to speak. Can this be done?
    Bam
    I don't know about the SQL server you are using, but I'm using mySQL and I can use the LEAST function

    select LEAST(10,IIF(......))

    -Chris
    Check out my SQL tutorial at http://www.bitesizeinc.net/index.php/sql.html

Posting Permissions

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