SitePoint Sponsor

User Tag List

Results 1 to 19 of 19

Thread: Help with query

  1. #1
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with query

    The following code gets the average number of hits over the logging period for each day of the week. How can I add up all 7 values and use that to find a percentage of each day? I am wanting to use the %'s in a graph.

    PHP Code:
    $sql "SELECT count(*) as cnt, DAYNAME(date) as dnr, 
    TO_DAYS(date) as tdr 
    FROM 
    $tblprefix.$dbsql 
    WHERE date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 day ) 
    GROUP BY dnr,tdr 
    ORDER BY tdr"

    $result mysql_query($sql); 
    echo 
    "<table border=\"0\" width=\"300\">";
    while ( 
    $row mysql_fetch_array$result ) ) { 
    echo 
    "<tr><td>$row[dnr]</td><td>$row[cnt]</td></tr>"

    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  2. #2
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use the SUM() aggregate function instead of COUNT().

    That should do it.
    The more time I save by not planning and documenting,
    the more time I have left to debug.


  3. #3
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I got an SQL error when i tried running that =/
    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  4. #4
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What version of MySQL are you running?
    The more time I save by not planning and documenting,
    the more time I have left to debug.


  5. #5
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about the AVG() function?
    The more time I save by not planning and documenting,
    the more time I have left to debug.


  6. #6
    SitePoint Evangelist jplush76's Avatar
    Join Date
    Nov 2003
    Location
    Los Angeles, CA
    Posts
    460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did you put a column you wanted SUM'd in the SUM() function?

    Example "SUM(columnA) as aTotal"
    My-Bic - Easiest AJAX/PHP Framework Around
    Now Debug PHP scripts with Firebug!

  7. #7
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yea i just replaced count(*) with sum(*).

    Im running mysql version 4.0.24-standard
    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  8. #8
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. I think I got this off track. If there is a field that has a total number of hits for a day or a field that has a number that you could add up to get a total then you would use the sum function with the name of that column inside it.

    If there is no such column then count(*) is what you want to use.

    Can you outline the definition of your table and / or give some sample data?
    The more time I save by not planning and documenting,
    the more time I have left to debug.


  9. #9
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, here is the relevant table info:
    PHP Code:
      `idint(11NOT NULL auto_increment,
      `
    datedate NOT NULL default '0000-00-00',
      
    PRIMARY KEY  (`id`) 
    and complete table info:
    PHP Code:
      `idint(11NOT NULL auto_increment,
      `
    browservarchar(255NOT NULL default '',
      `
    ipvarchar(15NOT NULL default '',
      `
    datedate NOT NULL default '0000-00-00',
      `
    refervarchar(255NOT NULL default '0',
      `
    ssvarchar(55NOT NULL default '',
      `
    srvarchar(55NOT NULL default '',
      `
    jevarchar(10NOT NULL default '',
      `
    domainvarchar(100NOT NULL default '',
      `
    timevarchar(15NOT NULL default '',
      
    PRIMARY KEY  (`id`) 
    The info in the date colum is just now(). (yyyy/mm/dd i think it is)
    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  10. #10
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Parry Sound, ON
    Posts
    725
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Upgrade to 4.1.x and you could do this in a subselect. Otherwise I think it's easiest to find the total in PHP.
    Thank you for wearing pants

  11. #11
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm lost on how to do it in php =/ Loops get me all confused
    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  12. #12
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok... use a sub query to get the total so you can do the calculation on each row in your result set...
    Code:
    SELECT (
    
    SELECT COUNT( * )
    FROM dt
    ) AS ttl, count( DAYNAME( date ) ) AS cnt, DAYNAME( date ) AS dnr, TO_DAYS( date ) AS tdr
    FROM dt
    WHERE date >= DATE_SUB( CURRENT_DATE, INTERVAL 6
    DAY )
    GROUP BY dnr, tdr
    ORDER BY tdr
    LIMIT 0 , 30
    I called the table dt in this example.
    The more time I save by not planning and documenting,
    the more time I have left to debug.


  13. #13
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To do it in php use the
    PHP Code:
    mysql_num_rows() 
    function to get your total then use that in each pass through the record set.
    The more time I save by not planning and documenting,
    the more time I have left to debug.


  14. #14
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql "SELECT count(*) as cnt, DAYNAME(date) as dnr, 
    TO_DAYS(date) as tdr 
    FROM 
    $tblprefix.$dbsql 
    WHERE date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 day ) 
    GROUP BY dnr,tdr 
    ORDER BY tdr"

    $result mysql_num_rows($sql); 
    echo 
    $result
    Tried that to see if anything was put out, but receive and error saying invalid result. Sorry im totally lost here.
    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  15. #15
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry. I wasn't clear.
    PHP Code:
    $result mysql_query($sql);

    $num mysql_num_rows($result);

    echo 
    $num
    The more time I save by not planning and documenting,
    the more time I have left to debug.


  16. #16
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, figured out a simple way of doing this:
    PHP Code:
    $total $total $row[cnt]; 
    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  17. #17
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now i just have to figure out how to split it up into days of week. Now i am totally lost

    I could create an array, day[0], where that number is the day of week, but i dont see how to do it inside a while loop
    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  18. #18
    SitePoint Guru godsfshrmn's Avatar
    Join Date
    Mar 2001
    Posts
    671
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I belive i have figured it out! Here is what i did:
    PHP Code:
    $sql "SELECT count(*) as cnt, DAYNAME(date) as dnr, 
    TO_DAYS(date) as tdr 
    FROM 
    $tblprefix.$dbsql 
    WHERE date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 day ) 
    GROUP BY dnr,tdr 
    ORDER BY tdr"

    $result mysql_query($sql); 
    echo 
    "<table border=\"0\" width=\"300\">";
    $total 0;
    $i 0;
    while ( 
    $row mysql_fetch_array$result ) ) { 
        echo 
    "<tr><td>$row[dnr]</td><td>$row[cnt]</td></tr>"
        
    $total $total $row[cnt];
        
    $day[$i] = $row['cnt'];
        
    $i++;

    echo 
    "</table>";
    echo 
    round(($day['0']/$total)*100) ; 
    s c r i p t s f o r y o u . n e t
    ScriptsForYou

  19. #19
    &lt;!-- Insert thoughts here --&gt; pitcher17's Avatar
    Join Date
    Apr 2004
    Location
    The great white north
    Posts
    293
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But your total is only going to be a total of the week up to that day. It won't be a total of the whole week. You would have to loop through the records twice. Once to get the total for the week and a second time to calculate the percentages.
    PHP Code:
    $strTotal "select count(*) as ttl 
    from 
    $tblprefix.$dbsql 
    where date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 day )"
    ;
    $totalHits mysql_result(mysql_query($strTotal),0);

     
    $sql "SELECT count(*) as cnt, DAYNAME(date) as dnr,
    TO_DAYS(date) as tdr
    FROM 
    $tblprefix.$dbsql
    WHERE date >= DATE_SUB(CURRENT_DATE, INTERVAL 6 day )
    GROUP BY dnr,tdr
    ORDER BY tdr"
    ;
    $result mysql_query($sql);

    echo 
    "<table border=\"0\" width=\"300\">"

    echo 
    "<tr>";
    echo 
    "<th>Day</th>";
    echo 
    "<th>Hits</th>";
    echo 
    "<th>Percentage</th>";
    echo 
    "</tr>";

    while ( 
    $row mysql_fetch_array$result ) ) {
        echo 
    "<tr><td>"$row['dnr'] . "</td>";
        echo 
    "<td>" $row['cnt'] . "</td>";
        echo 
    "<td>" $row['cnt'] / $totalHits "</td>";
        echo 
    "</tr>"
    }

    echo 
    "</table>"
    Or am I missing the point of what you are trying to do again?
    The more time I save by not planning and documenting,
    the more time I have left to debug.



Bookmarks

Posting Permissions

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