SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Mysql - get how many users have used my app only one.

    Hi all,
    in this table I log the users of an e-commerce
    PHP Code:
    CREATE TABLE IF NOT EXISTS user_access (
        
    id BIGINT UNSIGNED NOT NULL auto_increment,
        
    session_id CHAR(64NOT NULL,
        
    fb_id BIGINT unsigned DEFAULT NULL,
        
    browser_id SMALLINT NOT NULL,
        
    so_id SMALLINT NOT NULL,
        
    device_id SMALLINT NOT NULL,
        
    referer_id SMALLINT DEFAULT NULL,
        
    ip VARCHAR(15NOT NULL,
        
    log_fb_datetime DATETIME DEFAULT NULL,
        
    access_start_datetime DATETIME NOT NULL,
        
    access_end_datetime DATETIME DEFAULT NULL,
        
    country_code CHAR(2) DEFAULT NULL,
        
    country_name VARCHAR(255) DEFAULT NULL,
        
    region_code TINYINT DEFAULT NULL,
        
    region_name VARCHAR(255) DEFAULT NULL,
        
    city VARCHAR(255) DEFAULT NULL,
        
    lat FLOAT10) DEFAULT ,
        
    lng FLOAT10) DEFAULT 0,
        
    PRIMARY KEY  (id)
    ENGINE=InnoDB  DEFAULT CHARSET=utf8
    if the fb_id is null the user has never sign up to the app
    I want to find how many users have used my app only one
    and never logged in.
    My best but it doesn't work
    PHP Code:
    SELECT COUNT( * ) AS num
    FROM sharint_user_access
    WHERE fb_id IS NOT NULL 
    GROUP BY fb_id
    HAVING num 
    =
    Can you help me,please ?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT COUNT(*) AS num 
      FROM ( SELECT fb_id
               FROM sharint_user_access 
              WHERE fb_id IS NOT NULL  
             GROUP 
                 BY fb_id 
             HAVING COUNT(*) = 1 ) AS these_guys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks


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
  •