[bambamn007]

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

[MrWizard]

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

[MrWizard]

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.

[bambamn007]

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

[MrWizard]

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

[bambamn007]

LOL. I just read the part about charging me.

[bambamn007]

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

[bambamn007]

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

[MrWizard]

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

[bambamn007]

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

[MrWizard]

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

[bambamn007]

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

[MrWizard]

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

[bambamn007]

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

[MrWizard]

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