Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2003
    Posts
    32

    Unanswered: One last Question for SQL for now

    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 those points 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

  2. #2
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    After this one, I'm charging you...

    I think a sub-query like the following should do the trick for you....

    select name, sum(IIF(outcome='win',2,1)) as points FROM (Select Top 10 name, outcome FROM Results WHERE name='Bam' ORDER BY PlayDate)

    I didn't try this out at all, so although I'm pretty sure the syntax is correct, there may be some tweaking you'll have to do.

    Basically, since a SQL query outputs a table, that table can be used as the basis for another query.

    You didn't say if the order of the 10 records selected mattered at all, I'm supposing it does, and have included it above as the PlayDate field.

    Tim
    Tim

  3. #3
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    oops...

    Either leave out the name field in the top level select, or aggregate it as well... just noticed... sorry!

    SELECT sum(IIF(outcome='win',2,1)) as ......

    OR

    SELECT COUNT(name), sum(IIF(outcome='win',2,1)) as .....

    ought to do the trick.
    Tim

  4. #4
    Join Date
    Jan 2003
    Posts
    32
    Didn't work. This is what I have entered:

    SELECT SUM(IIF(outcome='win',2,1)) AS points FROM (SELECT Top 10 name, outcome FROM Results WHERE name='Bam' ORDER BY PlayDate)

    It was accepted as a good string in FrontPage, but when viewed on the website, I got the following:

    Database Results Error
    Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
    Number: -2147217904 (0x80040E10)
    Source: Microsoft OLE DB Provider for ODBC Drivers

    I don't know if that helps.. as it is greek to me, lol.
    Bam

  5. #5
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    ok...

    You should NOT have entered 'PLAYDATE'. That was just a made up field to show you how to order the subquery result. Try just the part...

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

    Tim
    Tim

  6. #6
    Join Date
    Jan 2003
    Posts
    32
    LOL. I just read the part about charging me.

  7. #7
    Join Date
    Jan 2003
    Posts
    32

    Smile

    That last one worked perfectly. Thank you so much for helping. And I will try to not have a question for a while

  8. #8
    Join Date
    Jan 2003
    Posts
    32
    ok. I need to know how much you will charge, cause this has led me to another problem or question.

    I have this which works great.

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

    And thanks for that. On another page. What if I wanted to include the actual number of matches played into this. I would still want the points to reflect the first 10 matches, but would like to know how many were actually played. So my table in FrontPage would have 2 columns one named "points" and another named "matches" Can this be done? I have been trying to get something to work since your last post, to no avail.
    I tried placing something along with that like
    SELECT SUM(IIF(name='Bam',1,1)) AS matches FROM Results

    But I can't seem to be able to join this into the above one to make it give me something that works. Any ideas?
    Bam

  9. #9
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    ok... once more then...

    What you want to do is actually join two queries together to get a result.

    There are a few ways to do this... but off the top of my head, I would first try....


    SELECT SUM(IIF(outcome='win',2,1)) AS points FROM (SELECT Top 10 name, outcome FROM Results WHERE name='Bam') UNION SELECT count(name) FROM Results WHERE name='Bam'

    If you do this, you'll get 2 records instead of just one. The total count of matches will be in the same field, but on the second record.


    Another way, which would get your results in a single record will only work if you have a unique key in your table (let's call it UKey for this example). The idea would be to join the table to itself....

    SELECT COUNT(r1.name) as TotMatches, SUM(IIF(r2.outcome='win',2,1)) AS points FROM results r1, (SELECT Top 10 name, outcome FROM Results WHERE name='Bam') r2 WHERE name='Bam' AND r1.UKey=r2.UKey

    BUT REMEMBER, this will only work if you have a unique key field that you can use. REPLACE Ukey (2times) above with the name of your unique key field.

    Hope one of these work for you.

    Tim
    Tim

  10. #10
    Join Date
    Jan 2003
    Posts
    32
    Ok. this is what I have:

    SELECT COUNT(r1.name) as TotMatches, SUM(IIF(r2.outcome='win',2,1)) AS points FROM results r1, (SELECT Top 10 name, outcome FROM Results WHERE name='Bam') r2 WHERE name='Bam' AND r1.ID=r2.ID

    my unique field is the ID field

    I get this error:

    [Microsoft][ODBC Microsoft Access Driver] The specified field 'name' could refer to more than one table listed in the FROM clause of your SQL statement.

    I would like to have it as 2 fields, if we can get this to work, but can get away with just 1.
    Bam

  11. #11
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    oops...

    The error is telling you that the field called NAME exists in both tables that we're joining... in this case, r1 and r2 are the same table, so of course the field exists in both. It just wants to know which table to use for the statement. In our case, the name='Bam' clause applies to table r1 in one case and r2 in the other... so:

    [Microsoft][ODBC Microsoft Access Driver] The specified field 'name' could refer to more than one table listed in the FROM clause of your SQL statement.

    Try this...

    SELECT COUNT(r1.name) as TotMatches, SUM(IIF(r2.outcome='win',2,1)) AS points FROM results r1, (SELECT Top 10 name, outcome FROM Results WHERE r2.name='Bam') r2 WHERE r1.name='Bam' AND r1.ID=r2.ID

    I do believe that ought to work.

    Tim
    Tim

  12. #12
    Join Date
    Jan 2003
    Posts
    32
    It worked fine when I verified it, but appeared as an error on the actual web page itself:

    Database Results Error
    Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
    Number: -2147217904 (0x80040E10)
    Source: Microsoft OLE DB Provider for ODBC Drivers

    If you want to try to find the problem, that would be great. BUt if not, I can go with the earlier statement, and I will understand.
    Thanks so much for all your help.
    Bam

  13. #13
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    One more try...

    This error is telling you that a field name in the query doesn't exist in the underlying table. Since the only thing we just did was add the r1 and r2 prefixes on the name field, let's try a variation and see what we get...

    First, make SURE you didn't accidently put r1,name instead of r1.name... or r2,name instead of r2.name CHECK ALL instances of r1.name and r2.name.

    If that checks out, then...

    First, try:

    SELECT COUNT(r1.name) as TotMatches, SUM(IIF(r2.outcome='win',2,1)) AS points FROM results r1, (SELECT Top 10 name, outcome FROM Results WHERE name='Bam') r2 WHERE r1.name='Bam' AND r1.ID=r2.ID


    If that doesn't work, then try:

    SELECT COUNT(r1.name) as TotMatches, SUM(IIF(r2.outcome='win',2,1)) AS points FROM results r1, (SELECT Top 10 r2.name, outcome FROM Results WHERE r2.name='Bam') r2 WHERE r1.name='Bam' AND r1.ID=r2.ID

    Let me know what you get.

    Tim
    Tim

  14. #14
    Join Date
    Jan 2003
    Posts
    32
    Ok. The first one gave me this error:

    Database Results Error
    Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
    Number: -2147217904 (0x80040E10)
    Source: Microsoft OLE DB Provider for ODBC Drivers


    The second one gave me this error:

    Database Results Error
    Description: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.
    Number: -2147217904 (0x80040E10)
    Source: Microsoft OLE DB Provider for ODBC Drivers

    I actually copied and pasted these into the DB, so no chance for error. They verify as being correct statements, but when viewed in the web, I get the errors.
    Bam

  15. #15
    Join Date
    Mar 2003
    Location
    Atlanta, GA
    Posts
    191

    Oh...

    MrWizard isn't feeling so bright today...

    I think I see the problem:

    Try...

    SELECT r1.id, r1.name, COUNT(r1.name) as TotMatches, SUM(IIF(r2.outcome='win',2,1)) AS points FROM results r1, (SELECT Top 10 id, name, outcome FROM Results WHERE name='Bam') r2 WHERE r1.name='Bam' AND r1.ID=r2.ID


    If this works, then you MAY be able to eliminate the "r1.id, r1.name" from the top level select clause. Try it and see.

    Tim
    Tim

Posting Permissions

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