Question about getting a specific count of a speci

1. Senior Member
Join Date
Dec 1969
Posts
1,323

Question about getting a specific count of a speci

i have a sql query sqlstr="select Distinct statLogin, statDate, statPage, count(statPage) as hits from stats where statDate &#062;= #" & startDate & "# and statDate &#060;= #" & endDate & "# group by statLogin, statDate, statPage"<BR><BR>And it returns all values for each day. Fine and dandy. I would like to modify this a bit, or add some code to the recordset that would allow me to get the following...<BR><BR>if statPage contains "request.asp" count up the hits and give me a total. If statPage contains "thank_you.asp" count up the hits and give me a total. Is that making sense? I tried<BR><BR>if Instr(1,rs("statPage"),"request_info.asp",1) then<BR> sTotalRequest = rs("hits") + rs("hits")<BR> end if<BR> <BR> if Instr(1,rs("statPage"),"thank_you.asp",1) then<BR> sTotalThankYou = rs("hits") + rs("hits")<BR> end if <BR><BR><BR>but do not get the right return vvalue<BR>

2. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

RE: Question about getting a specific count of a s

where..... and PageName="YourPageName.asp" group.....<BR><BR><BR><BR>that will give you the hits for that page...you can then do whatever you want to that value<BR><BR>If you dont have the column storing the PageName, nows the time to add it :)<BR><BR>

3. Senior Member
Join Date
Dec 1969
Posts
96,118

Basic math...

Let&#039;s suppose we have the following records returned (just showing the fields we care about):<BR><BR>hits -- statPage<BR> 7 -- whatever.html<BR> 9 -- foo/request_info.asp<BR> 11 -- junk.asp<BR> 3 -- zamboni/request_info.asp<BR> 8 -- fuzz.php<BR><BR>Okay, now look at your code. Just processing the "request_info.asp" in this loop, for clarity:<BR><BR>Do Until rs.EOF<BR> if Instr(1,rs("statPage"),"request_info.asp",1) then<BR> sTotalRequest = rs("hits") + rs("hits")<BR> end if<BR> rs.MoveNext<BR>Loop<BR><BR>Iteration 1: no match in statPage field.<BR>Iteration 2: match!<BR> sTotalRequest = rs("hits") + rs("hits")<BR> ==&#062;&#062; sTotalRequest = 9 + 9<BR> ==&#062;&#062; sTotalRequest = 18<BR>Iteration 3: no match in statPage field.<BR>Iteration 4: match!<BR> sTotalRequest = rs("hits") + rs("hits")<BR> ==&#062;&#062; sTotalRequest = 3 + 3<BR> ==&#062;&#062; sTotalRequest = 6<BR>Iteration 5: no match in statPage field.<BR><BR>End result: <BR> sTotalRequest is 6.<BR><BR>Ummmm.... Does that make sense to you?<BR><BR>I&#039;ll let you think on this a moment before we continue.<BR><BR><BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

Ummm...Akhilesh...

...you missed the biggy.<BR> sTotalRequest = rs("hits") + rs("hits")<BR>????<BR><BR>Besides, maybe he really *DOES* need to use INSTR?<BR><BR>(Which he could do in the query, yes, but he wants the totals for two *different* PARTIAL urls.)<BR><BR>

5. Senior Member
Join Date
Dec 1969
Posts
1,323

RE: Basic math...

yea, i changed sTotalRequest = rs("hits") + rs("hits") to<BR>sTotalRequest = rs("hits") + sTotalRequest and I was good to go. Thanks...

6. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

I dont get it

if he saves the NAME (id) of the page in the table then why the instr??<BR><BR>cane he just select THAT record and then add whatever to it<BR><BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
96,118

Yep...you could also...

...have done this in the SQL query. But it would make the query a lot slower and more complex.<BR><BR>If you are running through the RS for other reasons (e.g., to display the results in HTML), then this is a FINE way to do it.<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
96,118

To get the *TWO* total numbers that he wanted, you&#039;d have to do this in the SQL:<BR><BR>SELECT Sum( IIF( InStr(statPage,"abc.asp") &#062; 0, 1, 0 ) AS abcHits,<BR> Sum( IIF( InStr(statPage,"xyz.asp") &#062; 0, 1, 0 ) AS xyzHits<BR>FROM stats WHERE ...<BR><BR>And you&#039;d have to execute that query *IN ADDITION TO* the other query, if you needed the individual records of the other query for other reasons (which apparently he does, since he gets more fields than just statPage and hits!)<BR><BR>

9. Senior Member
Join Date
Dec 1969
Location
Los Angeles, CA
Posts
21,192

What??

so you saying he wants TO add the value to itself??<BR><BR>what do you want a 0 or a 1??<BR><BR><BR>ok forget it.....i have no idea what this is all about<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
96,118

See my 'basic math' post...

...for an example.<BR><BR>He wants the sum of the hits for *all* the counts where the page name *includes* the given string.<BR><BR>And he wants that sum for two *different* included strings.<BR><BR>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•