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).
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`)
My first query creates a view named memlog, joins the two tables, converts the DATETIME field to DATE, and sets the from - to dates.
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 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.
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)
Would appreciate suggestions.
SELECT DISTINCT DATE AS DATE,COUNT(m_id) AS members, STATUS
GROUP BY STATUS