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
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:
CREATE TABLE `ft_users` (
`ID` bigint(20) unsigned NOT NULL, *
`nome` varchar(30) COLLATE utf8_unicode_ci NOT NULL, *
`cognome` varchar(30) COLLATE utf8_unicode_ci NOT NULL, *
`nascita` date NOT NULL, *
`sesso` enum('male','female') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'male', *
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, *
`iscrizione` date NOT NULL, *
`country` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'it', *
`last_activity` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, *
`online` int(1) NOT NULL DEFAULT '1', *
`cdv_fb` int(1) NOT NULL DEFAULT '1', *
`cdv_email` int(1) NOT NULL DEFAULT '1', *
`profile_pic` int(1) NOT NULL DEFAULT '0', *
PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `ft_stats_access` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`session_id` char(64) NOT NULL,
`fb_id` bigint(20) unsigned DEFAULT NULL,
`domain_id` int(10) unsigned NOT NULL,
`browser_id` smallint(6) NOT NULL,
`so_id` smallint(6) NOT NULL,
`device_id` smallint(6) NOT NULL,
`referer_id` smallint(6) DEFAULT NULL,
`ip` varchar(15) NOT NULL,
`login_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(4) DEFAULT NULL,
`region_name` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`lat` float(10,6) DEFAULT '0.000000',
`lng` float(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` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
I manage this query
SELECT MAX(num),gender,browser,avgtime,age FROM(
SELECT U.sesso AS gender, COUNT(U.ID) AS num,B.title AS browser,
AVG( TIMESTAMPDIFF( MINUTE , A.access_start_datetime, A.access_end_datetime ) ) AS avgtime,
CASE
WHEN FLOOR( (TO_DAYS( NOW( ) ) - TO_DAYS( U.nascita ) ) / 365.25) <18
THEN '13-17'
WHEN FLOOR( (TO_DAYS( NOW( ) ) - TO_DAYS( U.nascita ) ) / 365.25) >17 <25
THEN '18-24'
WHEN FLOOR( (TO_DAYS( NOW( ) ) - TO_DAYS( U.nascita ) ) / 365.25) >24 <35
THEN '25-34'
WHEN FLOOR( (TO_DAYS( NOW( ) ) - TO_DAYS( U.nascita ) ) / 365.25) >34 <45
THEN '35-44'
WHEN FLOOR( (TO_DAYS( NOW( ) ) - TO_DAYS( U.nascita ) ) / 365.25) >35 <55
THEN '45-54'
WHEN FLOOR( (TO_DAYS( NOW( ) ) - TO_DAYS( U.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 ?