SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Total RecordCount...

    Code:
    data in myTable
    
    id    date         ip
    (1)  20050721 211.224.44.125 
    (2)  20050721 211.224.44.125 
    (3)  20050721 61.74.86.141 
    (4)  20050721 220.89.253.152 
    (5)  20050722 220.89.253.152 
    (6)  20050722 220.89.253.152 
    (7)  20050722 220.89.253.152 
    (8)  20050723 211.224.44.125 
    (9)  20050723 220.89.253.152 
    (10) 20050723 61.74.86.141
    
    code
    
    select date
         , count(distinct ip) as visitors
         , count(*) as pageViews
          from myTable
    group
        by date
    
    
    result
    
    date   visitors pageViews
    20050721  3        4
    20050722  1        3
    20050723  3        3
    I can produce the above result by the above code with the above data in myTable.


    How can I get the total recordCount of date, visitors and pageViews like the following quote--especially the total recordCoun of visitors.

    The total recordCount of date is '3.'
    The total recordCount of visitors is '7.'
    The total recordCount of pageViews is '10.'

    I like to use the total recordCounts above in making the following target.

    Code:
    date   visitors pageViews
    20050721    3      4
    20050722    1      3
    20050723    3      3
    --------------------
         3      7     10 --- total

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select count(*)        as TotalRecordCountOfDate 
         , sum(visitors)   as TotalRecordCountOfVisitors 
         , sum(pageviews)  as TotalRecordCountOfPageviews
      from (
           select date
                , count(distinct ip) as visitors
                , count(*) as pageViews
             from myTable
           group
               by date
           ) as foo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    select count(*)        as TotalRecordCountOfDate 
         , sum(visitors)   as TotalRecordCountOfVisitors 
         , sum(pageviews)  as TotalRecordCountOfPageviews
      from (
           select date
                , count(distinct ip) as visitors
                , count(*) as pageViews
             from myTable
           group
               by date
           ) as foo
    Your code seems logical.

    I did the following trial codes with your code.

    Code:
    trial code1
    
    select count(*)        as TotalRecordCountOfDate 
         , sum(visitors)   as TotalRecordCountOfVisitors 
         , sum(pageviews)  as TotalRecordCountOfPageviews
      from (
           select date
                , count(distinct ip) as visitors
                , count(*) as pageViews
             from myTable
           group
               by date
           ) as foo
    
    
    Syntax error or access violation:  ('select date , count(distinct ip) as visitors , count  
    
    trial code2
    
    select count(*)        as TotalRecordCountOfDate 
         , sum(visitors)   as TotalRecordCountOfVisitors 
         , sum(pageviews)  as TotalRecordCountOfPageviews
      from (
           select date
                , count(distinct ip) as visitors
                , count(*) as pageViews
             from myTable
           group
               by date
           ) 
    
    
    Syntax error or access violation:  ('select date , count(distinct ip) as visitors , count 
    
    trial code3
    
    select date
         , count(distinct ip) as visitors
         , count(*) as pageViews
         ,count(*)        as TotalRecordCountOfDate 
         , sum(visitors)   as TotalRecordCountOfVisitors 
         , sum(pageviews)  as TotalRecordCountOfPageviews
         from myTable
           group
            by date
    
    
    Column not found: Unknown column 'visitors' in 'field list' 
    Every trial above meets an error.
    What did I wrong in my applying your code?
    Last edited by dotJoon; Jul 27, 2005 at 18:36.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    gee, i'm sorry, my crystal ball (which runs on Windows MEŠ) is currently not working

    you will have to tell me what the error messages were in each case
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    gee, i'm sorry, my crystal ball (which runs on Windows MEŠ) is currently not working

    you will have to tell me what the error messages were in each case
    I modified the above trial code for telling the error messags which is written in red.(The error message is translating from my mother tongue to English.)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    let me guess as to the reason for the error in the first 2 trials

    have you read this thread?

    it's a "sticky" thread, located at the very top of the forum, you can't miss it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    let me guess as to the reason for the error in the first 2 trials

    have you read this thread?

    it's a "sticky" thread, located at the very top of the forum, you can't miss it

    I am in trouble. I am using MySQL Version 4.0.25.

    Version up to 4.1 is big risk to me...

    I am using one click installation of mySQL Version 4.0.25.

    The one click installation of mySQL doesn't provide mySQL 4.1.

    I have no confidence for installing mySQl 4.1. alone.


    I have one question about version up to mySQl 4.1.

    If I succeed in installation of mySQL 4.1, will all codes which are used in mySQL 4.0.25 do work fine?

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes, the only thing you have to worry about is creating new users. when you create a new mysql user, you have to go back and reset their password using the old_password() function. see http://dev.mysql.com/doc/mysql/en/old-client.html

    don't be scared by that. the only part you need to worry about is:
    #

    Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function:

    mysql> SET PASSWORD FOR
    -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

    Alternatively, use UPDATE and FLUSH PRIVILEGES:

    mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
    -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

    Substitute the password you want to use for ``newpwd'' in the preceding examples. MySQL cannot tell you what the original password was, so you'll need to pick a new one.


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
  •