Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Complicated inner join??

Reply
Thread Tools

Complicated inner join??

 
 
Jeff
Guest
Posts: n/a
 
      12-20-2005
Ok gang. Here is something complicated, well, at least to me anyway.
Using Access DB

I have a table in my DB called members. In that table, I have 2 tables I
will be using "username" and "points"

Now, I also have a table called all_matches. This table contains every match
report. Over 25,000 of them. I have a "username" field an "outcome" field an
"username1" field and "extra_match" field.

What I need to do, is make a query that takes the username from table
"members" and find :
using the "username" field in the members table, i need to obtain stats
that give me the following.

1.) how many total matches played. Which would simply be a count of the
username field in the all_matches table. Since people report the match win
or lose.
2.) how many wins, where extra_match = 0. This would be a count of username
in the all_matches table where "outcome" = 'Win' and extra_match=0.
3.) how many wins, where extra_match = 1. This would be a count of username
in the all_matches table where "outcome" = 'Win' and extra_match=1
4.) how many losses, where extra_match = 0. This would be a count of
username in the all_matches table where "outcome" = 'Lose' and
extra_match=0..
5.) how many losses, where extra_match = 1. This would be a count of
username in the all_matches table where "outcome" = 'Lose' and
extra_match=1.
6.) Then divide the wins into the total games and get a win %

now I did this, using top 50 so it wasn't too intensive, since there are so
many lines in the all_matches table. But what I can't do is get the sort
right. But I think if i can do this in access, instead of doing it on an asp
page, it will be easier.

I had one that was done for me on another DB, but I was unable to modify it
for this DB.
I can show you the script of what I did on the asp page, but it is rather
sloppy, and it doesn't work anyway. So I need help creating this SQL within
access as a query.

I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don't know inner
and outer joins.
THanks
bam


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-20-2005
Jeff wrote:
> Ok gang. Here is something complicated, well, at least to me anyway.
> Using Access DB
>
> I have a table in my DB called members. In that table, I have 2
> tables I will be using "username" and "points"


Please include datatypes in your descriptions of your tables

Table: Members
Columns: username Text
points Number? Long? Single?

>
> Now, I also have a table called all_matches. This table contains
> every match report. Over 25,000 of them. I have a "username" field an
> "outcome" field an "username1" field and "extra_match" field.


Same deal. What is the purpose of "username1"?

>
> What I need to do, is make a query that takes the username from table
> "members" and find :
> using the "username" field in the members table, i need to obtain
> stats that give me the following.
>
> 1.) how many total matches played. Which would simply be a count of
> the username field in the all_matches table. Since people report the
> match win or lose.


SELECT mb.username, count(m.username) as TotalMatches
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username


> 2.) how many wins, where extra_match = 0. This would be a count of
> username in the all_matches table where "outcome" = 'Win' and
> extra_match=0.


<the FROM and GROUP clauses will not change, so I will not repeat then>

SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins


> 3.) how many wins, where extra_match = 1. This would be a count of
> username in the all_matches table where "outcome" = 'Win' and
> extra_match=1


SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins



> 4.) how many losses, where extra_match = 0. This would be a count of
> username in the all_matches table where "outcome" = 'Lose' and
> extra_match=0..


SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses


> 5.) how many losses, where extra_match = 1. This would be a count of
> username in the all_matches table where "outcome" = 'Lose' and
> extra_match=1.


SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses


> 6.) Then divide the wins into the total games and get a win %


Is "wins" defined as TotalWins + TotalExtraWins? if so:
SELECT mb.username, count(m.username) as TotalMatches,
SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses,
(SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.username) as WinPercent
<snip>
> I know this is a lot,
> But any help would be appreciated. I know basic SQL, but I don't know
> inner and outer joins.



That IS "basic SQL" ...
It's the GROUP BY and the use of Iif that brings this into the "advanced"
realm.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
 
 
 
Jeff
Guest
Posts: n/a
 
      12-20-2005

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Jeff wrote:
>> Ok gang. Here is something complicated, well, at least to me anyway.
>> Using Access DB
>>
>> I have a table in my DB called members. In that table, I have 2
>> tables I will be using "username" and "points"

>
> Please include datatypes in your descriptions of your tables
>
> Table: Members
> Columns: username Text
> points Number? Long? Single?

ok. username is 55 ch text, points is long integer.

>
>>
>> Now, I also have a table called all_matches. This table contains
>> every match report. Over 25,000 of them. I have a "username" field an
>> "outcome" field an "username1" field and "extra_match" field.

>
> Same deal. What is the purpose of "username1"?


the purpose of username1 is that the person reporting is the username... the
person he is reporting that he played is username1. I took over this, and
haven't been able to sort everything out, and make it easier to read yet.

>
>>
>> What I need to do, is make a query that takes the username from table
>> "members" and find :
>> using the "username" field in the members table, i need to obtain
>> stats that give me the following.
>>
>> 1.) how many total matches played. Which would simply be a count of
>> the username field in the all_matches table. Since people report the
>> match win or lose.

>
> SELECT mb.username, count(m.username) as TotalMatches
> FROM members as mb left join matches as m
> ON mb.username= m.username
> GROUP BY mb.username
>
>
>> 2.) how many wins, where extra_match = 0. This would be a count of
>> username in the all_matches table where "outcome" = 'Win' and
>> extra_match=0.

>
> <the FROM and GROUP clauses will not change, so I will not repeat then>
>
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
>
>
>> 3.) how many wins, where extra_match = 1. This would be a count of
>> username in the all_matches table where "outcome" = 'Win' and
>> extra_match=1

>
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
>
>
>
>> 4.) how many losses, where extra_match = 0. This would be a count of
>> username in the all_matches table where "outcome" = 'Lose' and
>> extra_match=0..

>
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
>
>
>> 5.) how many losses, where extra_match = 1. This would be a count of
>> username in the all_matches table where "outcome" = 'Lose' and
>> extra_match=1.

>
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses
>
>
>> 6.) Then divide the wins into the total games and get a win %

>
> Is "wins" defined as TotalWins + TotalExtraWins? if so:


yes, TotalWins would be the total from extra_match = 0 and extra_match = 1.

> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses,
> (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
> count(m.username) as WinPercent
> <snip>
>> I know this is a lot,
>> But any help would be appreciated. I know basic SQL, but I don't know
>> inner and outer joins.

>
>
> That IS "basic SQL" ...
> It's the GROUP BY and the use of Iif that brings this into the "advanced"
> realm.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>


Ok, so I need to put all these into the sql in access. Hopefully nothing
changed with the info I gave after your questions. In all actuality, the
username1 shouldn't even come into play for this I don't think.
Thanks Bob
Jeff


 
Reply With Quote
 
Jeff
Guest
Posts: n/a
 
      12-20-2005
Now if I wanted to sort by TotalWins, would I just add that in before the
GROUP BY??


"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Jeff wrote:
>> Ok gang. Here is something complicated, well, at least to me anyway.
>> Using Access DB
>>
>> I have a table in my DB called members. In that table, I have 2
>> tables I will be using "username" and "points"

>
> Please include datatypes in your descriptions of your tables
>
> Table: Members
> Columns: username Text
> points Number? Long? Single?
>
>>
>> Now, I also have a table called all_matches. This table contains
>> every match report. Over 25,000 of them. I have a "username" field an
>> "outcome" field an "username1" field and "extra_match" field.

>
> Same deal. What is the purpose of "username1"?
>
>>
>> What I need to do, is make a query that takes the username from table
>> "members" and find :
>> using the "username" field in the members table, i need to obtain
>> stats that give me the following.
>>
>> 1.) how many total matches played. Which would simply be a count of
>> the username field in the all_matches table. Since people report the
>> match win or lose.

>
> SELECT mb.username, count(m.username) as TotalMatches
> FROM members as mb left join matches as m
> ON mb.username= m.username
> GROUP BY mb.username
>
>
>> 2.) how many wins, where extra_match = 0. This would be a count of
>> username in the all_matches table where "outcome" = 'Win' and
>> extra_match=0.

>
> <the FROM and GROUP clauses will not change, so I will not repeat then>
>
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
>
>
>> 3.) how many wins, where extra_match = 1. This would be a count of
>> username in the all_matches table where "outcome" = 'Win' and
>> extra_match=1

>
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
>
>
>
>> 4.) how many losses, where extra_match = 0. This would be a count of
>> username in the all_matches table where "outcome" = 'Lose' and
>> extra_match=0..

>
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
>
>
>> 5.) how many losses, where extra_match = 1. This would be a count of
>> username in the all_matches table where "outcome" = 'Lose' and
>> extra_match=1.

>
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses
>
>
>> 6.) Then divide the wins into the total games and get a win %

>
> Is "wins" defined as TotalWins + TotalExtraWins? if so:
> SELECT mb.username, count(m.username) as TotalMatches,
> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses,
> (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
> count(m.username) as WinPercent
> <snip>
>> I know this is a lot,
>> But any help would be appreciated. I know basic SQL, but I don't know
>> inner and outer joins.

>
>
> That IS "basic SQL" ...
> It's the GROUP BY and the use of Iif that brings this into the "advanced"
> realm.
>
> Bob Barrows
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-20-2005
Jeff wrote:

> Ok, so I need to put all these into the sql in access.


Actually, a better plan would be to create a saved query in Access. Call it
GetUserStats or something. In asp, simply call it like this:

set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;data source=..."
set rs=createobject("adodb.recordset")
cn.GetUserStats rs
'process the recordset

> Hopefully
> nothing changed with the info I gave after your questions.

Nope

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-20-2005
No. After. The ORDER BY always comes last. (The Access Query Builder allows
you to answer these types of questions yourself)

Jeff wrote:
> Now if I wanted to sort by TotalWins, would I just add that in before
> the GROUP BY??
>
>
> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Jeff wrote:
>>> Ok gang. Here is something complicated, well, at least to me anyway.
>>> Using Access DB
>>>
>>> I have a table in my DB called members. In that table, I have 2
>>> tables I will be using "username" and "points"

>>
>> Please include datatypes in your descriptions of your tables
>>
>> Table: Members
>> Columns: username Text
>> points Number? Long? Single?
>>
>>>
>>> Now, I also have a table called all_matches. This table contains
>>> every match report. Over 25,000 of them. I have a "username" field
>>> an "outcome" field an "username1" field and "extra_match" field.

>>
>> Same deal. What is the purpose of "username1"?
>>
>>>
>>> What I need to do, is make a query that takes the username from
>>> table "members" and find :
>>> using the "username" field in the members table, i need to obtain
>>> stats that give me the following.
>>>
>>> 1.) how many total matches played. Which would simply be a count of
>>> the username field in the all_matches table. Since people report the
>>> match win or lose.

>>
>> SELECT mb.username, count(m.username) as TotalMatches
>> FROM members as mb left join matches as m
>> ON mb.username= m.username
>> GROUP BY mb.username
>>
>>
>>> 2.) how many wins, where extra_match = 0. This would be a count of
>>> username in the all_matches table where "outcome" = 'Win' and
>>> extra_match=0.

>>
>> <the FROM and GROUP clauses will not change, so I will not repeat
>> then>
>>
>> SELECT mb.username, count(m.username) as TotalMatches,
>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
>>
>>
>>> 3.) how many wins, where extra_match = 1. This would be a count of
>>> username in the all_matches table where "outcome" = 'Win' and
>>> extra_match=1

>>
>> SELECT mb.username, count(m.username) as TotalMatches,
>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
>>
>>
>>
>>> 4.) how many losses, where extra_match = 0. This would be a count of
>>> username in the all_matches table where "outcome" = 'Lose' and
>>> extra_match=0..

>>
>> SELECT mb.username, count(m.username) as TotalMatches,
>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
>>
>>
>>> 5.) how many losses, where extra_match = 1. This would be a count of
>>> username in the all_matches table where "outcome" = 'Lose' and
>>> extra_match=1.

>>
>> SELECT mb.username, count(m.username) as TotalMatches,
>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
>> TotalExtraLosses
>>
>>
>>> 6.) Then divide the wins into the total games and get a win %

>>
>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
>> SELECT mb.username, count(m.username) as TotalMatches,
>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
>> count(m.username) as WinPercent
>> <snip>
>>> I know this is a lot,
>>> But any help would be appreciated. I know basic SQL, but I don't
>>> know inner and outer joins.

>>
>>
>> That IS "basic SQL" ...
>> It's the GROUP BY and the use of Iif that brings this into the
>> "advanced" realm.
>>
>> Bob Barrows
>>
>> --
>> Microsoft MVP -- ASP/ASP.NET
>> Please reply to the newsgroup. The email account listed in my From
>> header is my spam trap, so I don't check it very often. You will get
>> a quicker response by posting to the newsgroup.


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


 
Reply With Quote
 
Jeff
Guest
Posts: n/a
 
      12-20-2005
Thanks Bob, I actually did a build in access with this query, then call it
from the asp page.
YOUDAMAN!!



"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> No. After. The ORDER BY always comes last. (The Access Query Builder
> allows
> you to answer these types of questions yourself)
>
> Jeff wrote:
>> Now if I wanted to sort by TotalWins, would I just add that in before
>> the GROUP BY??
>>
>>
>> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> Jeff wrote:
>>>> Ok gang. Here is something complicated, well, at least to me anyway.
>>>> Using Access DB
>>>>
>>>> I have a table in my DB called members. In that table, I have 2
>>>> tables I will be using "username" and "points"
>>>
>>> Please include datatypes in your descriptions of your tables
>>>
>>> Table: Members
>>> Columns: username Text
>>> points Number? Long? Single?
>>>
>>>>
>>>> Now, I also have a table called all_matches. This table contains
>>>> every match report. Over 25,000 of them. I have a "username" field
>>>> an "outcome" field an "username1" field and "extra_match" field.
>>>
>>> Same deal. What is the purpose of "username1"?
>>>
>>>>
>>>> What I need to do, is make a query that takes the username from
>>>> table "members" and find :
>>>> using the "username" field in the members table, i need to obtain
>>>> stats that give me the following.
>>>>
>>>> 1.) how many total matches played. Which would simply be a count of
>>>> the username field in the all_matches table. Since people report the
>>>> match win or lose.
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches
>>> FROM members as mb left join matches as m
>>> ON mb.username= m.username
>>> GROUP BY mb.username
>>>
>>>
>>>> 2.) how many wins, where extra_match = 0. This would be a count of
>>>> username in the all_matches table where "outcome" = 'Win' and
>>>> extra_match=0.
>>>
>>> <the FROM and GROUP clauses will not change, so I will not repeat
>>> then>
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
>>>
>>>
>>>> 3.) how many wins, where extra_match = 1. This would be a count of
>>>> username in the all_matches table where "outcome" = 'Win' and
>>>> extra_match=1
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
>>>
>>>
>>>
>>>> 4.) how many losses, where extra_match = 0. This would be a count of
>>>> username in the all_matches table where "outcome" = 'Lose' and
>>>> extra_match=0..
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
>>>
>>>
>>>> 5.) how many losses, where extra_match = 1. This would be a count of
>>>> username in the all_matches table where "outcome" = 'Lose' and
>>>> extra_match=1.
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
>>> TotalExtraLosses
>>>
>>>
>>>> 6.) Then divide the wins into the total games and get a win %
>>>
>>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
>>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
>>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
>>> count(m.username) as WinPercent
>>> <snip>
>>>> I know this is a lot,
>>>> But any help would be appreciated. I know basic SQL, but I don't
>>>> know inner and outer joins.
>>>
>>>
>>> That IS "basic SQL" ...
>>> It's the GROUP BY and the use of Iif that brings this into the
>>> "advanced" realm.
>>>
>>> Bob Barrows
>>>
>>> --
>>> Microsoft MVP -- ASP/ASP.NET
>>> Please reply to the newsgroup. The email account listed in my From
>>> header is my spam trap, so I don't check it very often. You will get
>>> a quicker response by posting to the newsgroup.

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>



 
Reply With Quote
 
Jeff
Guest
Posts: n/a
 
      12-20-2005
Ok. Let me show you what I have now. Here is my script on the asp page:
<%
set admin5 = conn.execute("select top 50 * from final order by TotalWins
DESC")

numRealPos = 0
numDisplayPos = 1
do while not admin5.eof
var1 = admin5.fields.item("username").value
var2 = admin5.fields.item("TotalMatches").value
var3 = admin5.fields.item("TotalWins").value
var4 = admin5.fields.item("TotalExtraWIns").value
var5 = admin5.fields.item("TotalLosses").value
var6 = admin5.fields.item("TotalExtraLosses").value
var7 = admin5.fields.item("WinPercent").value

var8 = var3 + var4
var9 = var5 + var6

var10 = var3 * 5 ''total win points
var11 = var5 * 2 ''total losses points
var11a = var4 + var6 '' Total Extra Matches
var12 = var10 + var11 + var11a '' total points
var13 = (var8/var2) * 100
var14 = formatnumber(var13,0)

numRealPos = numRealPos + 1
If Not var1 = numLastValue Then
numLastValue = var3
numDisplayPos = numRealPos
End If

%>

<tr>
<td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
<td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
<td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
<td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
<td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
</tr>
<%
admin5.movenext
loop
%>
Now it starts off sorting correct, but 2 things appear to be working wrong.
First, it is displaying 51 records. Second, down around Rank 32, it starts
going off sort.
http://www.logotour.com/tour_rankings.asp
check out what I mean. I tried to put the order clause in the SQL in Access,
but then it wouldn't work. It prompted me for a Parameter.
Any Ideas??
Jeff


Now the problem is, the total
"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> No. After. The ORDER BY always comes last. (The Access Query Builder
> allows
> you to answer these types of questions yourself)
>
> Jeff wrote:
>> Now if I wanted to sort by TotalWins, would I just add that in before
>> the GROUP BY??
>>
>>
>> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> Jeff wrote:
>>>> Ok gang. Here is something complicated, well, at least to me anyway.
>>>> Using Access DB
>>>>
>>>> I have a table in my DB called members. In that table, I have 2
>>>> tables I will be using "username" and "points"
>>>
>>> Please include datatypes in your descriptions of your tables
>>>
>>> Table: Members
>>> Columns: username Text
>>> points Number? Long? Single?
>>>
>>>>
>>>> Now, I also have a table called all_matches. This table contains
>>>> every match report. Over 25,000 of them. I have a "username" field
>>>> an "outcome" field an "username1" field and "extra_match" field.
>>>
>>> Same deal. What is the purpose of "username1"?
>>>
>>>>
>>>> What I need to do, is make a query that takes the username from
>>>> table "members" and find :
>>>> using the "username" field in the members table, i need to obtain
>>>> stats that give me the following.
>>>>
>>>> 1.) how many total matches played. Which would simply be a count of
>>>> the username field in the all_matches table. Since people report the
>>>> match win or lose.
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches
>>> FROM members as mb left join matches as m
>>> ON mb.username= m.username
>>> GROUP BY mb.username
>>>
>>>
>>>> 2.) how many wins, where extra_match = 0. This would be a count of
>>>> username in the all_matches table where "outcome" = 'Win' and
>>>> extra_match=0.
>>>
>>> <the FROM and GROUP clauses will not change, so I will not repeat
>>> then>
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
>>>
>>>
>>>> 3.) how many wins, where extra_match = 1. This would be a count of
>>>> username in the all_matches table where "outcome" = 'Win' and
>>>> extra_match=1
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
>>>
>>>
>>>
>>>> 4.) how many losses, where extra_match = 0. This would be a count of
>>>> username in the all_matches table where "outcome" = 'Lose' and
>>>> extra_match=0..
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
>>>
>>>
>>>> 5.) how many losses, where extra_match = 1. This would be a count of
>>>> username in the all_matches table where "outcome" = 'Lose' and
>>>> extra_match=1.
>>>
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
>>> TotalExtraLosses
>>>
>>>
>>>> 6.) Then divide the wins into the total games and get a win %
>>>
>>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
>>> SELECT mb.username, count(m.username) as TotalMatches,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
>>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
>>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
>>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
>>> count(m.username) as WinPercent
>>> <snip>
>>>> I know this is a lot,
>>>> But any help would be appreciated. I know basic SQL, but I don't
>>>> know inner and outer joins.
>>>
>>>
>>> That IS "basic SQL" ...
>>> It's the GROUP BY and the use of Iif that brings this into the
>>> "advanced" realm.
>>>
>>> Bob Barrows
>>>
>>> --
>>> Microsoft MVP -- ASP/ASP.NET
>>> Please reply to the newsgroup. The email account listed in my From
>>> header is my spam trap, so I don't check it very often. You will get
>>> a quicker response by posting to the newsgroup.

>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>



 
Reply With Quote
 
Bob Lehmann
Guest
Posts: n/a
 
      12-21-2005
>> First, it is displaying 51 records.
That is because you have a tie somewhere. For example, if you had
1,2,3,3,4,5 as values, and selected top 5, you would get back 6 rows.

>> It prompted me for a Parameter.

Is "TotalWins" a column in your table?

PS - Quit using "*" for your SELECT. Explicitly name the columns you want
returned.

Bob Lehmann

"Jeff" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Ok. Let me show you what I have now. Here is my script on the asp page:
> <%
> set admin5 = conn.execute("select top 50 * from final order by TotalWins
> DESC")
>
> numRealPos = 0
> numDisplayPos = 1
> do while not admin5.eof
> var1 = admin5.fields.item("username").value
> var2 = admin5.fields.item("TotalMatches").value
> var3 = admin5.fields.item("TotalWins").value
> var4 = admin5.fields.item("TotalExtraWIns").value
> var5 = admin5.fields.item("TotalLosses").value
> var6 = admin5.fields.item("TotalExtraLosses").value
> var7 = admin5.fields.item("WinPercent").value
>
> var8 = var3 + var4
> var9 = var5 + var6
>
> var10 = var3 * 5 ''total win points
> var11 = var5 * 2 ''total losses points
> var11a = var4 + var6 '' Total Extra Matches
> var12 = var10 + var11 + var11a '' total points
> var13 = (var8/var2) * 100
> var14 = formatnumber(var13,0)
>
> numRealPos = numRealPos + 1
> If Not var1 = numLastValue Then
> numLastValue = var3
> numDisplayPos = numRealPos
> End If
>
> %>
>
> <tr>
> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
> solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
> solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
> solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
> solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
> </tr>
> <%
> admin5.movenext
> loop
> %>
> Now it starts off sorting correct, but 2 things appear to be working

wrong.
> First, it is displaying 51 records. Second, down around Rank 32, it starts
> going off sort.
> http://www.logotour.com/tour_rankings.asp
> check out what I mean. I tried to put the order clause in the SQL in

Access,
> but then it wouldn't work. It prompted me for a Parameter.
> Any Ideas??
> Jeff
>
>
> Now the problem is, the total
> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > No. After. The ORDER BY always comes last. (The Access Query Builder
> > allows
> > you to answer these types of questions yourself)
> >
> > Jeff wrote:
> >> Now if I wanted to sort by TotalWins, would I just add that in before
> >> the GROUP BY??
> >>
> >>
> >> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >>> Jeff wrote:
> >>>> Ok gang. Here is something complicated, well, at least to me anyway.
> >>>> Using Access DB
> >>>>
> >>>> I have a table in my DB called members. In that table, I have 2
> >>>> tables I will be using "username" and "points"
> >>>
> >>> Please include datatypes in your descriptions of your tables
> >>>
> >>> Table: Members
> >>> Columns: username Text
> >>> points Number? Long? Single?
> >>>
> >>>>
> >>>> Now, I also have a table called all_matches. This table contains
> >>>> every match report. Over 25,000 of them. I have a "username" field
> >>>> an "outcome" field an "username1" field and "extra_match" field.
> >>>
> >>> Same deal. What is the purpose of "username1"?
> >>>
> >>>>
> >>>> What I need to do, is make a query that takes the username from
> >>>> table "members" and find :
> >>>> using the "username" field in the members table, i need to obtain
> >>>> stats that give me the following.
> >>>>
> >>>> 1.) how many total matches played. Which would simply be a count of
> >>>> the username field in the all_matches table. Since people report the
> >>>> match win or lose.
> >>>
> >>> SELECT mb.username, count(m.username) as TotalMatches
> >>> FROM members as mb left join matches as m
> >>> ON mb.username= m.username
> >>> GROUP BY mb.username
> >>>
> >>>
> >>>> 2.) how many wins, where extra_match = 0. This would be a count of
> >>>> username in the all_matches table where "outcome" = 'Win' and
> >>>> extra_match=0.
> >>>
> >>> <the FROM and GROUP clauses will not change, so I will not repeat
> >>> then>
> >>>
> >>> SELECT mb.username, count(m.username) as TotalMatches,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
> >>>
> >>>
> >>>> 3.) how many wins, where extra_match = 1. This would be a count of
> >>>> username in the all_matches table where "outcome" = 'Win' and
> >>>> extra_match=1
> >>>
> >>> SELECT mb.username, count(m.username) as TotalMatches,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
> >>>
> >>>
> >>>
> >>>> 4.) how many losses, where extra_match = 0. This would be a count of
> >>>> username in the all_matches table where "outcome" = 'Lose' and
> >>>> extra_match=0..
> >>>
> >>> SELECT mb.username, count(m.username) as TotalMatches,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
> >>>
> >>>
> >>>> 5.) how many losses, where extra_match = 1. This would be a count of
> >>>> username in the all_matches table where "outcome" = 'Lose' and
> >>>> extra_match=1.
> >>>
> >>> SELECT mb.username, count(m.username) as TotalMatches,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
> >>> TotalExtraLosses
> >>>
> >>>
> >>>> 6.) Then divide the wins into the total games and get a win %
> >>>
> >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
> >>> SELECT mb.username, count(m.username) as TotalMatches,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
> >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
> >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
> >>> count(m.username) as WinPercent
> >>> <snip>
> >>>> I know this is a lot,
> >>>> But any help would be appreciated. I know basic SQL, but I don't
> >>>> know inner and outer joins.
> >>>
> >>>
> >>> That IS "basic SQL" ...
> >>> It's the GROUP BY and the use of Iif that brings this into the
> >>> "advanced" realm.
> >>>
> >>> Bob Barrows
> >>>
> >>> --
> >>> Microsoft MVP -- ASP/ASP.NET
> >>> Please reply to the newsgroup. The email account listed in my From
> >>> header is my spam trap, so I don't check it very often. You will get
> >>> a quicker response by posting to the newsgroup.

> >
> > --
> > Microsoft MVP -- ASP/ASP.NET
> > Please reply to the newsgroup. The email account listed in my From
> > header is my spam trap, so I don't check it very often. You will get a
> > quicker response by posting to the newsgroup.
> >
> >

>
>



 
Reply With Quote
 
Jeff
Guest
Posts: n/a
 
      12-21-2005

"Bob Lehmann" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>>> First, it is displaying 51 records.

> That is because you have a tie somewhere. For example, if you had
> 1,2,3,3,4,5 as values, and selected top 5, you would get back 6 rows.


I thought that might be the reason actually.
>
>>> It prompted me for a Parameter.

> Is "TotalWins" a column in your table?

Yes, TotalWins IS a field.
>
> PS - Quit using "*" for your SELECT. Explicitly name the columns you want

I am doing that because I do want all fields that are created in the SQL
that is in access. Is this still wrong??

> returned.
>
> Bob Lehmann
>
> "Jeff" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Ok. Let me show you what I have now. Here is my script on the asp page:
>> <%
>> set admin5 = conn.execute("select top 50 * from final order by TotalWins
>> DESC")
>>
>> numRealPos = 0
>> numDisplayPos = 1
>> do while not admin5.eof
>> var1 = admin5.fields.item("username").value
>> var2 = admin5.fields.item("TotalMatches").value
>> var3 = admin5.fields.item("TotalWins").value
>> var4 = admin5.fields.item("TotalExtraWIns").value
>> var5 = admin5.fields.item("TotalLosses").value
>> var6 = admin5.fields.item("TotalExtraLosses").value
>> var7 = admin5.fields.item("WinPercent").value
>>
>> var8 = var3 + var4
>> var9 = var5 + var6
>>
>> var10 = var3 * 5 ''total win points
>> var11 = var5 * 2 ''total losses points
>> var11a = var4 + var6 '' Total Extra Matches
>> var12 = var10 + var11 + var11a '' total points
>> var13 = (var8/var2) * 100
>> var14 = formatnumber(var13,0)
>>
>> numRealPos = numRealPos + 1
>> If Not var1 = numLastValue Then
>> numLastValue = var3
>> numDisplayPos = numRealPos
>> End If
>>
>> %>
>>
>> <tr>
>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
>> solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
>> solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
>> solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
>> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
>> solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
>> </tr>
>> <%
>> admin5.movenext
>> loop
>> %>
>> Now it starts off sorting correct, but 2 things appear to be working

> wrong.
>> First, it is displaying 51 records. Second, down around Rank 32, it
>> starts
>> going off sort.
>> http://www.logotour.com/tour_rankings.asp
>> check out what I mean. I tried to put the order clause in the SQL in

> Access,
>> but then it wouldn't work. It prompted me for a Parameter.
>> Any Ideas??
>> Jeff
>>
>>
>> Now the problem is, the total
>> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > No. After. The ORDER BY always comes last. (The Access Query Builder
>> > allows
>> > you to answer these types of questions yourself)
>> >
>> > Jeff wrote:
>> >> Now if I wanted to sort by TotalWins, would I just add that in before
>> >> the GROUP BY??
>> >>
>> >>
>> >> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
>> >> news:%(E-Mail Removed)...
>> >>> Jeff wrote:
>> >>>> Ok gang. Here is something complicated, well, at least to me anyway.
>> >>>> Using Access DB
>> >>>>
>> >>>> I have a table in my DB called members. In that table, I have 2
>> >>>> tables I will be using "username" and "points"
>> >>>
>> >>> Please include datatypes in your descriptions of your tables
>> >>>
>> >>> Table: Members
>> >>> Columns: username Text
>> >>> points Number? Long? Single?
>> >>>
>> >>>>
>> >>>> Now, I also have a table called all_matches. This table contains
>> >>>> every match report. Over 25,000 of them. I have a "username" field
>> >>>> an "outcome" field an "username1" field and "extra_match" field.
>> >>>
>> >>> Same deal. What is the purpose of "username1"?
>> >>>
>> >>>>
>> >>>> What I need to do, is make a query that takes the username from
>> >>>> table "members" and find :
>> >>>> using the "username" field in the members table, i need to obtain
>> >>>> stats that give me the following.
>> >>>>
>> >>>> 1.) how many total matches played. Which would simply be a count of
>> >>>> the username field in the all_matches table. Since people report the
>> >>>> match win or lose.
>> >>>
>> >>> SELECT mb.username, count(m.username) as TotalMatches
>> >>> FROM members as mb left join matches as m
>> >>> ON mb.username= m.username
>> >>> GROUP BY mb.username
>> >>>
>> >>>
>> >>>> 2.) how many wins, where extra_match = 0. This would be a count of
>> >>>> username in the all_matches table where "outcome" = 'Win' and
>> >>>> extra_match=0.
>> >>>
>> >>> <the FROM and GROUP clauses will not change, so I will not repeat
>> >>> then>
>> >>>
>> >>> SELECT mb.username, count(m.username) as TotalMatches,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
>> >>>
>> >>>
>> >>>> 3.) how many wins, where extra_match = 1. This would be a count of
>> >>>> username in the all_matches table where "outcome" = 'Win' and
>> >>>> extra_match=1
>> >>>
>> >>> SELECT mb.username, count(m.username) as TotalMatches,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
>> >>>
>> >>>
>> >>>
>> >>>> 4.) how many losses, where extra_match = 0. This would be a count of
>> >>>> username in the all_matches table where "outcome" = 'Lose' and
>> >>>> extra_match=0..
>> >>>
>> >>> SELECT mb.username, count(m.username) as TotalMatches,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
>> >>>
>> >>>
>> >>>> 5.) how many losses, where extra_match = 1. This would be a count of
>> >>>> username in the all_matches table where "outcome" = 'Lose' and
>> >>>> extra_match=1.
>> >>>
>> >>> SELECT mb.username, count(m.username) as TotalMatches,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
>> >>> TotalExtraLosses
>> >>>
>> >>>
>> >>>> 6.) Then divide the wins into the total games and get a win %
>> >>>
>> >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
>> >>> SELECT mb.username, count(m.username) as TotalMatches,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
>> >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
>> >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
>> >>> count(m.username) as WinPercent
>> >>> <snip>
>> >>>> I know this is a lot,
>> >>>> But any help would be appreciated. I know basic SQL, but I don't
>> >>>> know inner and outer joins.
>> >>>
>> >>>
>> >>> That IS "basic SQL" ...
>> >>> It's the GROUP BY and the use of Iif that brings this into the
>> >>> "advanced" realm.
>> >>>
>> >>> Bob Barrows
>> >>>
>> >>> --
>> >>> Microsoft MVP -- ASP/ASP.NET
>> >>> Please reply to the newsgroup. The email account listed in my From
>> >>> header is my spam trap, so I don't check it very often. You will get
>> >>> a quicker response by posting to the newsgroup.
>> >
>> > --
>> > Microsoft MVP -- ASP/ASP.NET
>> > Please reply to the newsgroup. The email account listed in my From
>> > header is my spam trap, so I don't check it very often. You will get a
>> > quicker response by posting to the newsgroup.
>> >
>> >

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
failing to instantiate an inner class because of order of inner classes Pyenos Python 2 12-27-2006 11:19 PM
Re: need help with a complicated stored procedure please iixv Microsoft Certification 0 07-30-2005 04:34 PM
Why all this quite complicated cisco stuff? Christian Knoblauch Cisco 7 12-23-2003 09:23 PM
Declaring ports with a complicated array type Marius Vollmer VHDL 6 12-10-2003 07:48 PM
inner classes in python as inner classes in Java Carlo v. Dango Python 14 10-19-2003 08:49 AM



Advertisments