SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

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)

    Count, max percentage

    Hi,
    first of all sorry for the object but
    I didn't find a better title ^^
    I've to populate a table
    click on this
    target.png
    it's the target of my site
    how many male , female
    what's the prevailing age of the user (not refer to gender)
    the browser used more
    and the the avg time
    and the relative percentages
    the scheme:
    PHP Code:
    CREATE TABLE `ft_users` ( 
        `
    IDbigint(20unsigned NOT NULL, *
        `
    nomevarchar(30COLLATE utf8_unicode_ci NOT NULL, *
        `
    cognomevarchar(30COLLATE utf8_unicode_ci NOT NULL, *
        `
    nascitadate NOT NULL, *
        `
    sessoenum('male','female'COLLATE utf8_unicode_ci NOT NULL DEFAULT 'male', *
        `
    emailvarchar(50COLLATE utf8_unicode_ci NOT NULL, *
        `
    iscrizionedate NOT NULL, *
        `
    countrychar(2COLLATE utf8_unicode_ci NOT NULL DEFAULT 'it', *
        `
    last_activitytimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, *
        `
    onlineint(1NOT NULL DEFAULT '1', *
        `
    cdv_fbint(1NOT NULL DEFAULT '1', *
        `
    cdv_emailint(1NOT NULL DEFAULT '1', *
        `
    profile_picint(1NOT NULL DEFAULT '0', *
        
    PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


    CREATE TABLE 
    `ft_stats_access` (
     `
    idbigint(20unsigned NOT NULL AUTO_INCREMENT,
     `
    session_idchar(64NOT NULL,
     `
    fb_idbigint(20unsigned DEFAULT NULL,
     `
    domain_idint(10unsigned NOT NULL,
     `
    browser_idsmallint(6NOT NULL,
     `
    so_idsmallint(6NOT NULL,
     `
    device_idsmallint(6NOT NULL,
     `
    referer_idsmallint(6) DEFAULT NULL,
     `
    ipvarchar(15NOT NULL,
     `
    login_fb_datetimedatetime DEFAULT NULL,
     `
    access_start_datetimedatetime NOT NULL,
     `
    access_end_datetimedatetime DEFAULT NULL,
     `
    country_codechar(2) DEFAULT NULL,
     `
    country_namevarchar(255) DEFAULT NULL,
     `
    region_codetinyint(4) DEFAULT NULL,
     `
    region_namevarchar(255) DEFAULT NULL,
     `
    cityvarchar(255) DEFAULT NULL,
     `
    latfloat(10,6) DEFAULT '0.000000',
     `
    lngfloat(10,6) DEFAULT '0.000000',
     
    PRIMARY KEY (`id`),
     
    KEY `index_stats_access_fb_id` (`fb_id`),
     
    KEY `index_stats_access_domain_id` (`domain_id`),
     
    KEY `index_stats_access_browser_id` (`browser_id`),
     
    KEY `index_stats_access_so_id` (`so_id`),
     
    KEY `index_stats_access_device_id` (`device_id`),
     
    KEY `index_stats_access_referer_id` (`referer_id`)
    ENGINE=InnoDB AUTO_INCREMENT=147913 DEFAULT CHARSET=utf8


    CREATE TABLE 
    `ft_stats_browser` (
     `
    idsmallint(5unsigned NOT NULL AUTO_INCREMENT,
     `
    titlevarchar(255NOT NULL,
     
    PRIMARY KEY (`id`)
    ENGINE=InnoDB AUTO_INCREMENT=DEFAULT CHARSET=utf8 

    I manage this query

    PHP Code:
    SELECT MAX(num),gender,browser,avgtime,age FROM(
    SELECT U.sesso AS genderCOUNT(U.ID) AS num,B.title AS browser,
    AVGTIMESTAMPDIFFMINUTE A.access_start_datetimeA.access_end_datetime ) ) AS avgtime,
    CASE 
        
    WHEN FLOOR( (TO_DAYSNOW( ) ) - TO_DAYSU.nascita ) ) / 365.25) <18
            THEN  
    '13-17'
        
    WHEN FLOOR( (TO_DAYSNOW( ) ) - TO_DAYSU.nascita ) ) / 365.25) >17 <25
            THEN  
    '18-24'
        
    WHEN FLOOR( (TO_DAYSNOW( ) ) - TO_DAYSU.nascita ) ) / 365.25) >24 <35
            THEN  
    '25-34'
        
    WHEN FLOOR( (TO_DAYSNOW( ) ) - TO_DAYSU.nascita ) ) / 365.25) >34 <45
            THEN  
    '35-44'
        
    WHEN FLOOR( (TO_DAYSNOW( ) ) - TO_DAYSU.nascita ) ) / 365.25) >35 <55
            THEN  
    '45-54'
        
    WHEN FLOOR( (TO_DAYSNOW( ) ) - TO_DAYSU.nascita ) ) / 365.25) >55 <65
            THEN  
    '55-64'
            
    ELSE  '65->'
            
    END AS age
    FROM  ft_users 
    AS U
    JOIN ft_stats_access 
    AS A ON U.ID A.fb_id
    JOIN ft_stats_browser 
    AS B ON A.browser_id B.id
    GROUP BY gender
    ,browser,age)
    AS 
    TARGET
    GROUP BY gender 
    but I've not idea how to get the percentage.

    Can you help me, please ?

  2. #2
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mmm I realized that query give me two rows of course so .....


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
  •