MySQL Advanced Queries
Get unique email count
SELECT COUNT(DISTINCT email) as uniqueEmailCount FROM members;
Get duplicate email count
SELECT COUNT(duplicatedEmails.idCount) as numberOfDuplicatedEmails FROM ( SELECT COUNT(id) as idCount FROM users GROUP BY email HAVING idCount > 1) as duplicatedEmails;
Get duplicate emails
SELECT email, COUNT(email) as emailCount FROM members GROUP BY email HAVING emailCount > 1;
How to update a table from another table?
Retreive inspectors that don’t have a job
SELECT DISTINCT(ins.name) FROM inspector as ins LEFT JOIN job as j ON j.inspector_id = ins.id WHERE j.id IS NULL;
Find the total number of jobs per day, ordered by chronological date.
SELECT DATE(service_date) AS _day, COUNT(id) AS numOfJobs FROM job GROUP BY DATE(service_date) ORDER BY _day