Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old July 23rd, 2003, 09:44 AM
Friend of Wrox
Points: 1,007, Level: 12
Points: 1,007, Level: 12 Points: 1,007, Level: 12 Points: 1,007, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 195
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default SUM Function

Can someone please help me with the right syntax on using the SUM Function?

This is my SQL code:

sqlString="SELECT TOP 78 * FROM dbo_work_detail WHERE member_id = " & oRSMem("MemberID") & " AND period_end_date < #" & strPenEffDate & "# ORDER BY units_2 DESC,period_end_date;"


What I am trying to do is get the sum of the field "units_2". I am trying to add the "SUM(units_2) as dblTotal" right after "TOP 78 *" in my SQL statement but I keep getting a syntax error. Could someone please help me with incorporating the SUM function in my SQL statement. Or is there a way where I can just sum the total of units_2 after I have retrieved all the records? Presently, the way I do it is I have to go through the recordset by looping through each record one by one. There are a lot of records returned in the recordset and it just slows the program down by looping through every records.


Thanks,
Judy
Reply With Quote
  #2 (permalink)  
Old July 23rd, 2003, 09:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Code:
sqlString="SELECT  TOP 78 SUM(units_2) FROM dbo_work_detail  WHERE member_id = " & oRSMem("MemberID") & " AND period_end_date < #" & strPenEffDate &  "# ORDER BY units_2 DESC,period_end_date;"
You cannot mix sums, averages, totals, etc. with regular rows (other colnames that are not toaled/averaged etc. including the '*'). If the other columns are all the same and you want to like them they you may have to do something like:

Code:
Min(colname)
Chris
Reply With Quote
  #3 (permalink)  
Old July 23rd, 2003, 10:13 AM
Friend of Wrox
Points: 1,007, Level: 12
Points: 1,007, Level: 12 Points: 1,007, Level: 12 Points: 1,007, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 195
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

I tried to that too just selected the column names instead of the asterisk but this is the error message I got when I ran it.

You tried to execute a query that does not include the specified expression 'units_2' as part of an aggregate function.

I also tried to use SUM function after I got the recordset (so I don't have to mess with the SQL statement anymore) by just executing the below command:

dblTotal = SUM(oRSWD("units_2")

but it gave me an error too.

Thanks,
Judy
Reply With Quote
  #4 (permalink)  
Old July 23rd, 2003, 10:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Code:
sqlString="SELECT  TOP 78 SUM(units_2) FROM dbo_work_detail  WHERE member_id = " & oRSMem("MemberID") & " AND period_end_date < #" & strPenEffDate &  "#"
If units_2 is an actual column, then this should work. I did make one change. I took the order by statement out since it was not needed because you will only get 1 row back.

Are you using SQL 2000 or Access. If Access, then the '#' are OK for the dates. If it is SQL 2000, then you need to send it in like it is a string using single quotes.

Chris
Reply With Quote
  #5 (permalink)  
Old July 23rd, 2003, 10:32 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Code:
sqlString="SELECT  TOP 78 SUM(units_2) as DBtotal FROM dbo_work_detail  WHERE member_id = " & oRSMem("MemberID") & " AND period_end_date < #" & strPenEffDate &  "#"
made 1 more change. Added the "as DBtotal " into the Statement. This names the column "DBtotal" and not "units_2"

Chris
Reply With Quote
  #6 (permalink)  
Old July 23rd, 2003, 11:05 AM
Friend of Wrox
Points: 1,007, Level: 12
Points: 1,007, Level: 12 Points: 1,007, Level: 12 Points: 1,007, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 195
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

I tried it and it is not bringing back anything at all. I made sure that there were records prior to changing the SQL statement. I ran it with the original SQL statement that I posted earlier. TOP 78 * was bringing back all dates between period_end_date and strpeneffdate.

This program was already working with the original SELECt statement that I have. I just wanted to make it more efficient because the way I get the total of units_2 is I loop through all the records returned by the query. I have this code below to get the total of units_2 after executing the query:

      dblTotalGross = 0
      Do While NOT oRSWD.EOF
            dblTotal = dblTotal + oRSWD("units_2")
            oRSWD.MoveNext
      Loop
      dblAMS = dblTotal / 36

I tried using the SUM function in my SELECT statement but I was always getting error messages. So I decided to just go with what will work. Now I have time to play with it since I have one that already works. Can you guys please be patient with me in finding out why dblTotal is not getting anything back from the new SELECT statement please?

Thanks,
Judy
Reply With Quote
  #7 (permalink)  
Old July 24th, 2003, 03:46 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Judy,

Quote:
quote:
Can you guys please be patient with me in finding out why dblTotal is
 not getting anything back from the new SELECT statement please?
kilika is trying to help you but you are not giving him much to go on.
He has given you what looks, to me, like a perfectly valid SQL statement,
but you just say "it doesn't work" - that's not very helpful.

Can you give us the structure (field names and data types) of this table dbo_work_detail?
(BTW thats an odd name for a table, are you sure that's what its called?
Or is it a table called work_detail which is owned by dbo?)

Also, can you show us what the actual sql contains after the values for the id and date have been substitiuted into it?

With this info it should be a piece of cake to figure out what's going wrong.

rgds
Phil
Reply With Quote
  #8 (permalink)  
Old July 24th, 2003, 08:52 AM
Friend of Wrox
Points: 1,007, Level: 12
Points: 1,007, Level: 12 Points: 1,007, Level: 12 Points: 1,007, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 195
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

Hi Phil,

I am thankful to kilika for the help and you are right it is a perfectly good SQL. Just not retrieving anything back. So let me answer your questions.

The actual sql does not contain any value after id and date have been substituted. I placed a response.write "Total " & dbltotal & "<BR>" right after the query and nothing came back but the word "dbltotal" which means that the querey did not work.

The database I am using is ACCESS and yes the table name is dbo_work_detail. The reason it has dbo in it is I am importing this table from Sybase which has dbo as it's owner but when imported to ACCESS it became dbo_work_detail.

Table Structure:
Field Name Data Type
member_id Long Integer
period_end_date Date/Time
units_2 Currency

Thanks,
Judy

member_id
Reply With Quote
  #9 (permalink)  
Old July 24th, 2003, 09:03 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:
The actual sql does not contain any value after id and date have been substituted.
I placed a response.write "Total " & dbltotal & "<BR>" right after the query and
nothing came back but the word "dbltotal" which means that the querey did not work.
Hang on, wasn't "dblTotal" the variable you were using in your previous code,
where you looped through summing the values yourself?

Surely with this new query you want to use:
Response.Write("Total=" & whateverYourRsIsCalled("DBtotal"))

maybe you should also post some other stuff like:
1. the whole code that executes the query and retrieves the records
2. the result of Response.Write(sqlString), so we can see what SQL you are actually sending to Access.

I'm increasingly beginning to think that the problem doesn't lie with the SQL statement...

rgds
Phil
Reply With Quote
  #10 (permalink)  
Old July 24th, 2003, 10:09 AM
Friend of Wrox
Points: 1,007, Level: 12
Points: 1,007, Level: 12 Points: 1,007, Level: 12 Points: 1,007, Level: 12
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: , , USA.
Posts: 195
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to jmss66
Default

Hi Phil,

You are right. I was not displaying it right by just doing response.write dbltotal. Dummy me...I have the result back as I displayed it correctly using the RS I was using Response.write oRSWD("dbltotal").

I have one problem with the figures though. If I use the original SQL statement I have been using, I get a different figure. Let me explain what I am trying to get in my original SQL statement. I have more than 78 records in a table and what I am trying to come up with is the Top 78 records with the highest pay(units_2) and I need to arrange them in a Descending order according to pay period(period_end_date). Eventually I will need to display all pay period and pay(but I can still use this SQL statement later in my program when I need to display the records). I still need to get the total(dbltotal) of all pay that is why I am looping through all the records and adding units_2 to dbltotal. Here is the original code:

Set oRSWD=Server.CreateObject("ADODB.Recordset")
sqlString="SELECT TOP 78 * FROM dbo_work_detail WHERE member_id = " & oRSMem("MemberID") & " AND period_end_date < #" & strPenEffDate & "# ORDER BY units_2 DESC,period_end_date;"
oRSWD.Open sqlString, objConn,adOpenKeyset,adLockOptimistic

      dblTotal = 0
      Do While NOT oRSWD.EOF
            dblTotal = dblTotal + oRSWD("units_2")
            oRSWD.MoveNext
      Loop
      dblAMS = dblTotal / 36

      Response.Write "dblTotal " & dblTotal & "<br>"
      The result of dblTotal is 122321.44


Here is the modified SQL that kilika sent:

sqlString="SELECT TOP 78 SUM(units_2) as dblTotal FROM dbo_work_detail WHERE member_id = " & oRSMem("MemberID") & " AND period_end_date < #" & strPenEffDate & "#"

      Response.Write "dbltotal " & orswd("dbltotal") & "<BR>"
      The result of dbltotal is 440653.41

I dump the table for this member and manually got the Top 78 and it added to 122321.44 which is the result of the original sql statement. So here I am trying to figure out why I got 440653.41 on the other one. Although I am very glad that I am making progress with both you guys' help. Do you have any idea why they have a different result?

Thanks,
Judy
Reply With Quote
Reply


Thread Tools
Display Modes

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 Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
sum function pcase XSLT 2 January 2nd, 2008 04:51 PM
sum() function felixm_jr Reporting Services 1 April 22nd, 2007 01:59 AM
Xpath: sum function gracehanh XSLT 13 September 27th, 2005 09:30 AM
sum function problem sherr8 Access 1 February 13th, 2004 02:06 PM
Need Help with the Sum Function athanatos XSLT 1 July 22nd, 2003 10:06 AM



All times are GMT -4. The time now is 07:50 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.