Hi,
my table
CREATE TABLE IF NOT EXISTS si_stats_access (
id BIGINT UNSIGNED NOT NULL auto_increment,
session_id CHAR(64) NOT NULL,
fb_id BIGINT unsigned DEFAULT NULL,
domain_id INT UNSIGNED NOT NULL,
browser_id SMALLINT NOT NULL,
so_id SMALLINT NOT NULL,
device_id SMALLINT NOT NULL,
referer_id SMALLINT DEFAULT NULL,
ip VARCHAR(15) NOT 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 FLOAT( 10, 6 ) DEFAULT 0 ,
lng FLOAT( 10, 6 ) DEFAULT 0,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
my query
SELECT 1 as stats_type_id, domain_id, DATE (access_start_datetime) as date, COUNT (id) as value
FROM si_stats_access
GROUP BY domain_id, DATE(access_start_datetime);
for example instead of
1 1 2013-03-11 100
1 2 2013-03-11 100
1 3 2013-03-11 100
I got
1 1 2013-03-11 100
I got only the rows for a domain
btw this seems to work but I don’t see the point
SELECT 1 AS stats_type_id, domain_id, DATE( access_start_datetime ) AS DATE, COUNT( id ) AS value
FROM si_stats_access
GROUP BY DATE( access_start_datetime ) , domain_id
i’m sorry, i can’t understand this – the 2nd column here is your domain, and it would not have completely dropped domains 2 and 3, since domain is in the GROUP BY
by the way, the rows that a GROUP BY query produces are ~not~ the same as the rows in the table
this is exactly the same as the other query, just with the order of the GROUP BY columns reversed, which makes no difference to the actual results produced… except that they might be presented in a different order
