distinct and sum

Hey gang. I have a question or two here.
i know this isn't sql NG, but I can't find one for what i want, and since i
am also using asp..thought I could try you guys.

i am using access db with asp scripting and
NOT using asp.net

I need to pull 2 things using a query.
distinct(username) and sum(iscore) and have it sort by iscore.
i tried this.

set name = conn.execute ("select distinct(username), sum(iscore) as pnts
from rounds where iweek = " & var1 & " order by pnts desc")

but i get the following:

'80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '(sum(iscore) as pnts)'.

/roughriders/PGA/main.asp, line 62

is there a way to do this?

what i have is a table that members enter scores. the fields are username
iweek iscore

so what i am doing is displaying the names, with the total of iscore where
the iweek matches the criteria

please help if you can
Jeff

Re: distinct and sum

"Jeff" wrote in message
news:2MqdnUB5d61yUhLfRVn-gw@adelphia.com...
> Hey gang. I have a question or two here.
> i know this isn't sql NG, but I can't find one for what i want, and since
> i am also using asp..thought I could try you guys.
>
> i am using access db with asp scripting and
> NOT using asp.net
>
> I need to pull 2 things using a query.
> distinct(username) and sum(iscore) and have it sort by iscore.
> i tried this.
>
> set name = conn.execute ("select distinct(username), sum(iscore) as pnts
> from rounds where iweek = " & var1 & " order by pnts desc")
>
> but i get the following:

DISTINCT is a modifier to the SELECT clause, not an aggregate function. Try
this:

select username, sum(iscore) as pnts from rounds where [...] group by
username

-Mark


> '80040e14'
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> in query expression '(sum(iscore) as pnts)'.
>
> /roughriders/PGA/main.asp, line 62
>
> is there a way to do this?
>
> what i have is a table that members enter scores. the fields are username
> iweek iscore
>
> so what i am doing is displaying the names, with the total of iscore where
> the iweek matches the criteria
>
> please help if you can
> Jeff
>

Re: distinct and sum

works like a charm.. thanks a bunch!


"Mark J. McGinty" wrote in message
news:uXEisCnXFHA.3572@TK2MSFTNGP12.phx.gbl...
>
> "Jeff" wrote in message
> news:2MqdnUB5d61yUhLfRVn-gw@adelphia.com...
>> Hey gang. I have a question or two here.
>> i know this isn't sql NG, but I can't find one for what i want, and since
>> i am also using asp..thought I could try you guys.
>>
>> i am using access db with asp scripting and
>> NOT using asp.net
>>
>> I need to pull 2 things using a query.
>> distinct(username) and sum(iscore) and have it sort by iscore.
>> i tried this.
>>
>> set name = conn.execute ("select distinct(username), sum(iscore) as pnts
>> from rounds where iweek = " & var1 & " order by pnts desc")
>>
>> but i get the following:
>
> DISTINCT is a modifier to the SELECT clause, not an aggregate function.
> Try this:
>
> select username, sum(iscore) as pnts from rounds where [...] group by
> username
>
> -Mark
>
>
>> '80040e14'
>> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
>> in query expression '(sum(iscore) as pnts)'.
>>
>> /roughriders/PGA/main.asp, line 62
>>
>> is there a way to do this?
>>
>> what i have is a table that members enter scores. the fields are username
>> iweek iscore
>>
>> so what i am doing is displaying the names, with the total of iscore
>> where the iweek matches the criteria
>>
>> please help if you can
>> Jeff
>>
>
>

Re: distinct and sum

now, if i wanted to sort by the pnts value, how would I do that without
getting a syntax error??
ie
select username, sum(iscore) as pnts from rounds where iweek = " & var1 & "
group by username order by pnts desc

i tried putting the order by clause in front of the group, and that didnt
work either.
thanks a bunch


"Jeff" wrote in message
news:yeydnS6GbfUGig3fRVn-gQ@adelphia.com...
> works like a charm.. thanks a bunch!
>
>
> "Mark J. McGinty" wrote in message
> news:uXEisCnXFHA.3572@TK2MSFTNGP12.phx.gbl...
>>
>> "Jeff" wrote in message
>> news:2MqdnUB5d61yUhLfRVn-gw@adelphia.com...
>>> Hey gang. I have a question or two here.
>>> i know this isn't sql NG, but I can't find one for what i want, and
>>> since i am also using asp..thought I could try you guys.
>>>
>>> i am using access db with asp scripting and
>>> NOT using asp.net
>>>
>>> I need to pull 2 things using a query.
>>> distinct(username) and sum(iscore) and have it sort by iscore.
>>> i tried this.
>>>
>>> set name = conn.execute ("select distinct(username), sum(iscore) as pnts
>>> from rounds where iweek = " & var1 & " order by pnts desc")
>>>
>>> but i get the following:
>>
>> DISTINCT is a modifier to the SELECT clause, not an aggregate function.
>> Try this:
>>
>> select username, sum(iscore) as pnts from rounds where [...] group by
>> username
>>
>> -Mark
>>
>>
>>> '80040e14'
>>> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
>>> operator) in query expression '(sum(iscore) as pnts)'.
>>>
>>> /roughriders/PGA/main.asp, line 62
>>>
>>> is there a way to do this?
>>>
>>> what i have is a table that members enter scores. the fields are
>>> username iweek iscore
>>>
>>> so what i am doing is displaying the names, with the total of iscore
>>> where the iweek matches the criteria
>>>
>>> please help if you can
>>> Jeff
>>>
>>
>>
>
>

Re: distinct and sum

Jeff wrote:
> now, if i wanted to sort by the pnts value, how would I do that
> without getting a syntax error??
> ie
> select username, sum(iscore) as pnts from rounds where iweek = " &
> var1 & " group by username order by pnts desc
>
You have to repeat the expression:

.... order by sum(iscore) desc

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: distinct and sum

"Jeff" wrote in message
news:GfCdnXVnq_hLzw3fRVn-sw@adelphia.com...
> now, if i wanted to sort by the pnts value, how would I do that without
> getting a syntax error??
> ie
> select username, sum(iscore) as pnts from rounds where iweek = " & var1 &
> " group by username order by pnts desc

Column aliases aren't recognized within a single rowset-returning statement,
so you could do either this:

select username, sum(iscore) as pnts from rounds where iweek = ?
group by username order by sum(iscore) desc

Or this

select username, pnts from (
select username, sum(iscore) as pnts from rounds where iweek = ?
group by username
) anylegalalias order by pnts desc

The inner select statement within the latter of those two suggestions is
called a derived rowset, which can be used basically anywhere a table or
view could be used, i.e., after FROM or JOIN ON. (Some restrictions apply,
your mileage may be less.)

The reason I used a ? in place of your concatenation has to do with avoiding
SQL injection, the ? is a place-holder for a parameter -- as is usual for
me, I've assumed you're using SQL Server... oh damn, you're using Jet?
Sadly, I have deep religious convictions that prevent me from doing anything
to proliferate the usage of Jet, particularly in a server environment. When
using Jet in any scenario that involves concurrency, it's not "if" it will
bring you grief, rather, "when and how much."

There are numerous discussions of this topic in this NG, search for keyword
"parameter" for relevant posts. And lastly the links that follow (courtesy
of Bob Barrows) talk about why SQL injection is not a good thing.


-Mark

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf





> i tried putting the order by clause in front of the group, and that didnt
> work either.
> thanks a bunch
>
>
> "Jeff" wrote in message
> news:yeydnS6GbfUGig3fRVn-gQ@adelphia.com...
>> works like a charm.. thanks a bunch!
>>
>>
>> "Mark J. McGinty" wrote in message
>> news:uXEisCnXFHA.3572@TK2MSFTNGP12.phx.gbl...
>>>
>>> "Jeff" wrote in message
>>> news:2MqdnUB5d61yUhLfRVn-gw@adelphia.com...
>>>> Hey gang. I have a question or two here.
>>>> i know this isn't sql NG, but I can't find one for what i want, and
>>>> since i am also using asp..thought I could try you guys.
>>>>
>>>> i am using access db with asp scripting and
>>>> NOT using asp.net
>>>>
>>>> I need to pull 2 things using a query.
>>>> distinct(username) and sum(iscore) and have it sort by iscore.
>>>> i tried this.
>>>>
>>>> set name = conn.execute ("select distinct(username), sum(iscore) as
>>>> pnts from rounds where iweek = " & var1 & " order by pnts desc")
>>>>
>>>> but i get the following:
>>>
>>> DISTINCT is a modifier to the SELECT clause, not an aggregate function.
>>> Try this:
>>>
>>> select username, sum(iscore) as pnts from rounds where [...] group by
>>> username
>>>
>>> -Mark
>>>
>>>
>>>> '80040e14'
>>>> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
>>>> operator) in query expression '(sum(iscore) as pnts)'.
>>>>
>>>> /roughriders/PGA/main.asp, line 62
>>>>
>>>> is there a way to do this?
>>>>
>>>> what i have is a table that members enter scores. the fields are
>>>> username iweek iscore
>>>>
>>>> so what i am doing is displaying the names, with the total of iscore
>>>> where the iweek matches the criteria
>>>>
>>>> please help if you can
>>>> Jeff
>>>>
>>>
>>>
>>
>>
>
>

Re: distinct and sum

well.. it is working, regardless of religious beliefs..lol
thanks a million for that.


"Mark J. McGinty" wrote in message
news:Jp0ke.21929$gp.20759@fed1read03...
>
> "Jeff" wrote in message
> news:GfCdnXVnq_hLzw3fRVn-sw@adelphia.com...
>> now, if i wanted to sort by the pnts value, how would I do that without
>> getting a syntax error??
>> ie
>> select username, sum(iscore) as pnts from rounds where iweek = " & var1 &
>> " group by username order by pnts desc
>
> Column aliases aren't recognized within a single rowset-returning
> statement, so you could do either this:
>
> select username, sum(iscore) as pnts from rounds where iweek = ?
> group by username order by sum(iscore) desc
>
> Or this
>
> select username, pnts from (
> select username, sum(iscore) as pnts from rounds where iweek = ?
> group by username
> ) anylegalalias order by pnts desc
>
> The inner select statement within the latter of those two suggestions is
> called a derived rowset, which can be used basically anywhere a table or
> view could be used, i.e., after FROM or JOIN ON. (Some restrictions
> apply, your mileage may be less.)
>
> The reason I used a ? in place of your concatenation has to do with
> avoiding SQL injection, the ? is a place-holder for a parameter -- as is
> usual for me, I've assumed you're using SQL Server... oh damn, you're
> using Jet? Sadly, I have deep religious convictions that prevent me from
> doing anything to proliferate the usage of Jet, particularly in a server
> environment. When using Jet in any scenario that involves concurrency,
> it's not "if" it will bring you grief, rather, "when and how much."
>
> There are numerous discussions of this topic in this NG, search for
> keyword "parameter" for relevant posts. And lastly the links that follow
> (courtesy of Bob Barrows) talk about why SQL injection is not a good
> thing.
>
>
> -Mark
>
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
> http://www.nextgenss.com/papers/advanced_sql_injection.pdf
> http://www.nextgenss.com/papers/more_advanced_sql_injection. pdf
> http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf
>
>
>
>
>
>> i tried putting the order by clause in front of the group, and that didnt
>> work either.
>> thanks a bunch
>>
>>
>> "Jeff" wrote in message
>> news:yeydnS6GbfUGig3fRVn-gQ@adelphia.com...
>>> works like a charm.. thanks a bunch!
>>>
>>>
>>> "Mark J. McGinty" wrote in message
>>> news:uXEisCnXFHA.3572@TK2MSFTNGP12.phx.gbl...
>>>>
>>>> "Jeff" wrote in message
>>>> news:2MqdnUB5d61yUhLfRVn-gw@adelphia.com...
>>>>> Hey gang. I have a question or two here.
>>>>> i know this isn't sql NG, but I can't find one for what i want, and
>>>>> since i am also using asp..thought I could try you guys.
>>>>>
>>>>> i am using access db with asp scripting and
>>>>> NOT using asp.net
>>>>>
>>>>> I need to pull 2 things using a query.
>>>>> distinct(username) and sum(iscore) and have it sort by iscore.
>>>>> i tried this.
>>>>>
>>>>> set name = conn.execute ("select distinct(username), sum(iscore) as
>>>>> pnts from rounds where iweek = " & var1 & " order by pnts desc")
>>>>>
>>>>> but i get the following:
>>>>
>>>> DISTINCT is a modifier to the SELECT clause, not an aggregate function.
>>>> Try this:
>>>>
>>>> select username, sum(iscore) as pnts from rounds where [...] group by
>>>> username
>>>>
>>>> -Mark
>>>>
>>>>
>>>>> '80040e14'
>>>>> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
>>>>> operator) in query expression '(sum(iscore) as pnts)'.
>>>>>
>>>>> /roughriders/PGA/main.asp, line 62
>>>>>
>>>>> is there a way to do this?
>>>>>
>>>>> what i have is a table that members enter scores. the fields are
>>>>> username iweek iscore
>>>>>
>>>>> so what i am doing is displaying the names, with the total of iscore
>>>>> where the iweek matches the criteria
>>>>>
>>>>> please help if you can
>>>>> Jeff
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>