select distinct / total query in MySQL

Results 1 to 2 of 2

Thread: select distinct / total query in MySQL

  1. #1
    Join Date
    Dec 1969

    Default select distinct / total query in MySQL

    Using MySQL<BR><BR>I have a simple site tracker. Each page that is viewed gets logged in a database.<BR><BR>I am outputting the data, and wish, for each day, to display the total number of pages viewed, and by how many distinct IP addresses.<BR><BR>To do this, I loop through each day:<BR><BR>and for each day, run this SQL to get the number of pages viewed per ip address for that day:<BR><BR>SELECT DISTINCT <BR>fldip, COUNT(fldid) as page_ct <BR>FROM _vtracker <BR>WHERE fld_date = &#039;"&the_date&"&#039; <BR>GROUP BY fldip<BR><BR>This returns another recordset of results. I could loop through this recordset, and total up the pages viewed per IP address, so that the end result would be something like:<BR><BR>27-October-2003 219 page hits viewed by 21 IP addresses<BR><BR>e.g. <BR><BR>x = 0<BR>do while not ctRS.EOF<BR>y = X + ctRS("page_ct")<BR>ctRS.Movenext<BR>Loop<BR>ctRS.C lose:set ctRS=nothing<BR><BR>However this does not work, as I get a type mismatch error, due for some reason to do with the fact that it doesn&#039;t like the ctrs("page_ct") variable.<BR><BR>Also, I know such a practice is frowned upon, so I have been looking into if there is a way I can do this using SQL.<BR><BR>What would be really good would be if I could just query the database to return the total number of unique IP addresses for a particular day.<BR><BR>something, to paraphrase, like:<BR><BR>SELECT TOTAL NUMBER OF DISTINCT fldip FROM _vtracker WHERE fld_date = &#039;"&the_date&"&#039;<BR><BR>Thanks<BR><BR>J im

  2. #2
    Join Date
    Dec 1969

    Default DISTINCT is a mistake

    You shouldn&#039;t be using both DISTINCT and GROUP BY. One or the other.<BR><BR>In other DBs you could probably do this one query with a nested SELECT, but MySQL doesn&#039;t support that.<BR><BR>I think your looping approach is quite adequate. I don&#039;t know why you are getting a type mismatch error (aside from the coding mistake you showed there), because it should work fine.<BR><BR>&#060;%<BR>...<BR>count = 0<BR>total = 0<BR>Do Until ctRS.EOF<BR> count = count + 1<BR> total = total + CLNG( ctRS("page_ct")<BR> ctRS.MoveNext<BR>Loop<BR>Response.Write total & " page hits viewed by " & count & " IP addresses"<BR>%&#062;<BR><BR>If you think that the count of IP addresses might get to be more than a couple of hundred, then maybe using a temp table would be a better approach.<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