-- ANSWER IS AT THE MOST BOTTOM------------------------------------------------------------------------ CREATE TABLE i_users(userId BIGINT PRIMARY KEY IDENTITY, username varchar(30), emailaddress varchar(30),isActive BIT DEFAULT 1 , password1 varchar(50), gender varchar(1)); CREATE TABLE i_user_login_logs(userID BIGINT, FOREIGN KEY(userID) REFERENCES i_users(userId), login_date DATE); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Administrator','Admin@pi.works.net',1,'1234',''); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Johnathan','Johnathan@gmail.com',1,'1234','M'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Rita','Rita@yahoo.com',1,'1234','F'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Christopher','Christopher@hotmail.com',1,'1234','M'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Kenzo','Kenzo@gmail.com',1,'1234','M'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Brent','Brent@gmail.com',0,'1234','M'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Adrianna','Adrianna@hotmail.com',0,'1234','F'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Fahem','Fahem@gmail.com',1,'1234','M'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Dimitri','Dimitri@gmail.com',0,'1234','M'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Fred','Fred@gmail.com',1,'1234','M'); INSERT INTO i_users(username,emailaddress,isActive,password1,gender) VALUES('Lily','Lily@gmail.com',1,'1234','F'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(1,'02.10.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(1,'02.25.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(1,'05.10.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(1,'09.25.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(3,'03.18.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(2,'06.26.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(2,'12.20.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(2,'11.20.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(5,'07.21.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(7,'08.04.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(9,'10.17.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(5,'10.26.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(4,'11.30.2016'); INSERT INTO i_user_login_logs(userId,login_date) VALUES(2,'01.24.2017'); SELECT COUNT(l.userId) AS #ofLogTimes,l.userId,u.username FROM i_users as u, i_user_login_logs as l WHERE u.userId=l.userId GROUP BY l.userId,u.username HAVING COUNT(l.userID)>3; -- This is for displaying which ones are greater than 3 SELECT DISTINCT COUNT(*) as Counts FROM (SELECT COUNT(l.userId) AS #ofLogTimes,l.userId,u.username FROM i_users as u, i_user_login_logs as l WHERE u.userId=l.userId GROUP BY l.userId,u.username HAVING COUNT(l.userID)>3) as s; -- This is the ANSWER. It displays how many different users logged in more than 3 times