stumped on sql...

using SQLServer.
i have a db table called Orders. It has a collumn called user_alpha. The
values in that collumn are A for one row, ABC for another, and C for the
other row. I need to know how to return the number 5 because that is how many
letters there are total for all the rows. It is based on an OrderID and the
rows are the LineID.

So how do i select the collumn based on OrderID and have it return the total
number of letters in all the rows, in this example 5 ??

sd

Re: stumped on sql...

SELECT SumAlphaLength = SUM(LEN(LTRIM(RTRIM(user_alpha))))
FROM Orders
WHERE OrderID = 5;



"sdmusicmaker" wrote in message
news:6125DACA-3DCD-4535-8C5B-4539B884737E@microsoft.com...
> using SQLServer.
> i have a db table called Orders. It has a collumn called user_alpha. The
> values in that collumn are A for one row, ABC for another, and C for the
> other row. I need to know how to return the number 5 because that is how
> many
> letters there are total for all the rows. It is based on an OrderID and
> the
> rows are the LineID.
>
> So how do i select the collumn based on OrderID and have it return the
> total
> number of letters in all the rows, in this example 5 ??
>
> sd

Re: stumped on sql...

sdmusicmaker wrote:
> using SQLServer.

Which version? It may be relevant, so you should supply it as a matter
of course.

> i have a db table called Orders. It has a collumn called user_alpha.
> The values in that collumn are A for one row, ABC for another, and C
> for the other row. I need to know how to return the number 5 because
> that is how many letters there are total for all the rows. It is
> based on an OrderID and the rows are the LineID.
>
> So how do i select the collumn based on OrderID and have it return
> the total number of letters in all the rows, in this example 5 ??
>
select sum(len(user_alpha)) as lettercount
from orders
where orderid = ...

--
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.

Re: stumped on sql...

Aaron,

what if i need to sum the 'qty column where the 'user_alpha' column is null
or has no value and where OrderID = a session variable called SessiionWebID?
The idea id get the sum of all the qty for rows where the 'user_alpha' column
was never writen to, and then based on the session variable WebID.

Any thoughts and does it need a group by or something?

sd


"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT SumAlphaLength = SUM(LEN(LTRIM(RTRIM(user_alpha))))
> FROM Orders
> WHERE OrderID = 5;
>
>
>
> "sdmusicmaker" wrote in message
> news:6125DACA-3DCD-4535-8C5B-4539B884737E@microsoft.com...
> > using SQLServer.
> > i have a db table called Orders. It has a collumn called user_alpha. The
> > values in that collumn are A for one row, ABC for another, and C for the
> > other row. I need to know how to return the number 5 because that is how
> > many
> > letters there are total for all the rows. It is based on an OrderID and
> > the
> > rows are the LineID.
> >
> > So how do i select the collumn based on OrderID and have it return the
> > total
> > number of letters in all the rows, in this example 5 ??
> >
> > sd
>
>
>

Re: stumped on sql...

> what if i need to sum the 'qty column where the 'user_alpha' column is
> null

Are you trying to sum the qty column or the number of characters in
user_alpha? Sounds like two different queries to me.

Anyway, if user_alpha can be NULL it will still work (you will get a warning
about null values being eliminated from an aggregate or set operation).

> The idea id get the sum of all the qty for rows where the 'user_alpha'
> column
> was never writen to, and then based on the session variable WebID.

CREATE PROCEDURE dbo.Order_GetAlphaLength
@OrderID INT
AS
BEGIN
SET NOCOUNT ON;

SELECT TotalQty = SUM(qty)
FROM dbo.Orders
WHERE OrderID = @OrderID
AND LEN(LTRIM(user_alpha)) = 0;
END
GO

Then, see the links Bob Barrows posted earlier today about how to use a
stored procedure from ASP. I have some material on it but it's quite dated.

Re: stumped on sql...

> AND LEN(LTRIM(user_alpha)) = 0;

Sorry, here you'll need:

AND LEN(LTRIM(COALESCE(user_alpha,''))) = 0;

Re: stumped on sql...

Start by testing your query in Query Analyzer (assuming there is an
orderid 5):
Select sum(qty) as TotalQty
From Orders
Where user_alpha > '' AND
OrderID = 5

After testing it in Query Analyzer and making sure it works, it is time
to decide how to expose it to client applications. My preference would
be to create a stored procedure* like this:

CREATE PROCEDURE GetTotalQty (
@OrderID int) --it IS an int, right?
AS
Select sum(qty) as TotalQty
From Orders
Where user_alpha > '' AND
OrderID = @OrderID
go
--test it like this:
exec GetTotalQty 5

Does it work? Good. Time to run it in ASP:

<%
dim cn, rs, orderid, totalqty
orderid=session("WebID")
if len(orderid) > 0 then
if isnumeric(orderid) then
orderid=clng(orderid)
set cn=createobject("adodb.connection")
cn.open "provider=sqloledb;" & _
"data source=yoursqlserver;" & _
"initial catalog=yourdatabase;" & _
"user id=username;" & _
"password=xxxxxx"
set rs=createobject("adodb.recordset")
cn.GetTotalQty orderid,rs
'normally I would check rs.eof here, but this
'query will ALWAYS return one record
totalqty=rs(0)
rs.close:set rs=nothing
cn.close: set cn=nothing
'do whatever you intend with totalqty
else
response.write orderid & " is not numeric"
end if
else
response.write "No Order ID"
end if
%>


If you would prefer not to use a stored procedure, then this alternative
will work safely:

<%
dim cn, rs, orderid, totalqty, sql, arparms,cmd
orderid=session("WebID")
if len(orderid) > 0 then
if isnumeric(orderid) then
arparms=array(clng(orderid))
sql="Select sum(qty) as TotalQty From Orders " & _
"Where user_alpha > '' AND OrderID = ?"
set cn=createobject("adodb.connection")
cn.open "provider=sqloledb;" & _
"data source=yoursqlserver;" & _
"initial catalog=yourdatabase;" & _
"user id=username;" & _
"password=xxxxxx"
set cmd=createobject("adodb.command")
cmd.Commandtype=1 'adCmdType
cmd.CommandText=sql
set cmd.activeconnection=cn
set rs=cmd.execute(, arparms)
'normally I would check rs.eof here, but this
'query will ALWAYS return one record
totalqty=rs(0)
rs.close:set rs=nothing
cn.close: set cn=nothing
'do whatever you intend with totalqty
else
response.write orderid & " is not numeric"
end if
else
response.write "No Order ID"
end if
%>

HTH,
Bob Barrows

*Actually, since this is returning a single value, i would use an output
parameter rather than a resultset, but I would rather not get into that
now. You should do some research


sdmusicmaker wrote:
> Aaron,
>
> what if i need to sum the 'qty column where the 'user_alpha' column
> is null or has no value and where OrderID = a session variable called
> SessiionWebID? The idea id get the sum of all the qty for rows where
> the 'user_alpha' column was never writen to, and then based on the
> session variable WebID.
>
> Any thoughts and does it need a group by or something?
>
> sd
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> SELECT SumAlphaLength = SUM(LEN(LTRIM(RTRIM(user_alpha))))
>> FROM Orders
>> WHERE OrderID = 5;
>>
>>


--
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.

Re: stumped on sql...

what is the difference of using SUM(LEN(LTRIM(RTRIM(user_alpha))))
versus SUM(LEN(TRIM(user_alpha))) ?? meaning, why the need for a LTRIM and
a LTRIM ??

"Aaron Bertrand [SQL Server MVP]" wrote in message
news:ebYM2ukiGHA.4304@TK2MSFTNGP03.phx.gbl...
> SELECT SumAlphaLength = SUM(LEN(LTRIM(RTRIM(user_alpha))))
> FROM Orders
> WHERE OrderID = 5;
>
>
>
> "sdmusicmaker" wrote in message
> news:6125DACA-3DCD-4535-8C5B-4539B884737E@microsoft.com...
>> using SQLServer.
>> i have a db table called Orders. It has a collumn called user_alpha. The
>> values in that collumn are A for one row, ABC for another, and C for the
>> other row. I need to know how to return the number 5 because that is how
>> many
>> letters there are total for all the rows. It is based on an OrderID and
>> the
>> rows are the LineID.
>>
>> So how do i select the collumn based on OrderID and have it return the
>> total
>> number of letters in all the rows, in this example 5 ??
>>
>> sd
>
>

Re: stumped on sql...

eeerrmm Ltrim and Rtrim i mean


"Jeff" wrote in message
news:QdCdnQ3m-fbQ3RrZnZ2dnUVZ_tudnZ2d@adelphia.com...
> what is the difference of using SUM(LEN(LTRIM(RTRIM(user_alpha))))
> versus SUM(LEN(TRIM(user_alpha))) ?? meaning, why the need for a LTRIM and
> a LTRIM ??
>
> "Aaron Bertrand [SQL Server MVP]" wrote in
> message news:ebYM2ukiGHA.4304@TK2MSFTNGP03.phx.gbl...
>> SELECT SumAlphaLength = SUM(LEN(LTRIM(RTRIM(user_alpha))))
>> FROM Orders
>> WHERE OrderID = 5;
>>
>>
>>
>> "sdmusicmaker" wrote in message
>> news:6125DACA-3DCD-4535-8C5B-4539B884737E@microsoft.com...
>>> using SQLServer.
>>> i have a db table called Orders. It has a collumn called user_alpha. The
>>> values in that collumn are A for one row, ABC for another, and C for the
>>> other row. I need to know how to return the number 5 because that is how
>>> many
>>> letters there are total for all the rows. It is based on an OrderID and
>>> the
>>> rows are the LineID.
>>>
>>> So how do i select the collumn based on OrderID and have it return the
>>> total
>>> number of letters in all the rows, in this example 5 ??
>>>
>>> sd
>>
>>
>
>

Re: stumped on sql...

If user_alpha is CHAR, it will count 'abc ' as 4 characters.



"Jeff" wrote in message
news:QdCdnQ3m-fbQ3RrZnZ2dnUVZ_tudnZ2d@adelphia.com...
> what is the difference of using SUM(LEN(LTRIM(RTRIM(user_alpha))))
> versus SUM(LEN(TRIM(user_alpha))) ?? meaning, why the need for a LTRIM and
> a LTRIM ??
>
> "Aaron Bertrand [SQL Server MVP]" wrote in
> message news:ebYM2ukiGHA.4304@TK2MSFTNGP03.phx.gbl...
>> SELECT SumAlphaLength = SUM(LEN(LTRIM(RTRIM(user_alpha))))
>> FROM Orders
>> WHERE OrderID = 5;
>>
>>
>>
>> "sdmusicmaker" wrote in message
>> news:6125DACA-3DCD-4535-8C5B-4539B884737E@microsoft.com...
>>> using SQLServer.
>>> i have a db table called Orders. It has a collumn called user_alpha. The
>>> values in that collumn are A for one row, ABC for another, and C for the
>>> other row. I need to know how to return the number 5 because that is how
>>> many
>>> letters there are total for all the rows. It is based on an OrderID and
>>> the
>>> rows are the LineID.
>>>
>>> So how do i select the collumn based on OrderID and have it return the
>>> total
>>> number of letters in all the rows, in this example 5 ??
>>>
>>> sd
>>
>>
>
>