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