Need count of unique visitors

Databases
1

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`)
)


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 first query creates a view named memlog, joins the two tables, converts the DATETIME field to DATE, and sets the from - to dates.

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)

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 DISTINCT DATE AS DATE,COUNT(m_id) AS members, STATUS
FROM memlog
GROUP BY STATUS

Would appreciate suggestions.

Andy

2

You should group by date, not by status :slight_smile:

3

then you should GROUP BY status

:slight_smile: