SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Apr 2003
    Posts
    332
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need count of unique visitors

    Trying to get a count of unique logins. My members often log in and out several times per day. I need a count of the number of unique visitors by category(status).

    Code:
    CREATE TABLE `mem_hours` (
    	`l_id` INT(10) NOT NULL AUTO_INCREMENT,
    	`m_id` INT(10) NOT NULL,
    	`timein` DATETIME NOT NULL,
    	`timeout` DATETIME NULL DEFAULT '0000-00-00 00:00:00',
    	`logout` VARCHAR(2) NOT NULL DEFAULT 'C',
    	PRIMARY KEY (`l_id`)
    )
    Code:
    CREATE TABLE `members` (
    	`m_id` INT(10) NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR(60) NOT NULL,
    	`status` VARCHAR(10) NOT NULL DEFAULT 'Verified',
    	PRIMARY KEY (`m_id`)
    )
    My first query creates a view named memlog, joins the two tables, converts the DATETIME field to DATE, and sets the from - to dates.
    Code:
    select cast(`mh`.`timein` as date) AS `date`,`mh`.`timein` AS `timein`,`m`.`m_id` AS `m_id`,`m`.`status` AS `status` from (`mem_hours` `mh` join `members` `m` on((`mh`.`m_id` = `m`.`m_id`))) where (`mh`.`timein` between 20110101000000 and 20110131235959)
    My query is supposed to select distinct dates and count members. However, the count total is equal to the original logins and not effected by distinct or group.
    Code:
    SELECT DISTINCT DATE AS DATE,COUNT(m_id) AS members, STATUS
    FROM memlog
    GROUP BY STATUS
    Would appreciate suggestions.

    Andy

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,081
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You should group by date, not by status
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by 1andyw View Post
    I need a count of the number of unique visitors by category(status).
    then you should GROUP BY status

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •