London Smith London Smith - 1 month ago 7
SQL Question

Multiple count(*) with left join in one shot

I have 2 tables like this:

Table users:

+--------------------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------+------+-----+---------+----------------+
| user_id | int(8) unsigned | NO | PRI | NULL | auto_increment |
| user_email | varchar(40) | NO | UNI | | |
| user_login | varchar(30) | YES | | NULL | |
| user_password | varchar(40) | YES | | NULL | |
| user_firstname | varchar(30) | YES | | NULL | |
| user_lastname | varchar(50) | YES | | NULL | |
+--------------------------+-----------------+------+-----+---------+----------------+


Table users_oauth to link users with oauth, if there is no oauth entry for a user, the user has created an account with an email/password:

+----------------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-----------------+------+-----+---------+----------------+
| oauth_id | int(8) unsigned | NO | PRI | NULL | auto_increment |
| oauth_user_id | int(8) unsigned | NO | MUL | NULL | |
| oauth_google_id | varchar(30) | YES | UNI | NULL | |
| oauth_facebook_id | varchar(30) | YES | UNI | NULL | |
| oauth_windowslive_id | varchar(30) | YES | UNI | NULL | |
+----------------------+-----------------+------+-----+---------+----------------+


For a count between two dates, to know how many new users I do the following for facebook oauth:

SELECT date_format(`user_date_accountcreated`, "%Y-%m-%d") AS date, COUNT(*) AS total FROM users
LEFT JOIN users_oauth ON users_oauth.oauth_user_id = users.user_id
WHERE (user_date_accountcreated BETWEEN '2016-10-01 00:00:00' AND '2016-10-15 23:59:59') AND oauth_facebook_id IS NOT NULL
GROUP BY year(user_date_accountcreated), month(user_date_accountcreated), day(user_date_accountcreated)


And another request for new users with google oauth, the only difference is
oauth_google_id IS NOT NULL
instead of
oauth_facebook_id IS NOT NULL
:

SELECT date_format(`user_date_accountcreated`, "%Y-%m-%d") AS date, COUNT(*) AS total FROM users
LEFT JOIN users_oauth ON users_oauth.oauth_user_id = users.user_id
WHERE (user_date_accountcreated BETWEEN '2016-10-01 00:00:00' AND '2016-10-15 23:59:59') AND oauth_google_id IS NOT NULL
GROUP BY year(user_date_accountcreated), month(user_date_accountcreated), day(user_date_accountcreated)


And the last one for windows live oauth:

SELECT date_format(`user_date_accountcreated`, "%Y-%m-%d") AS date, COUNT(*) AS total FROM users
LEFT JOIN users_oauth ON users_oauth.oauth_user_id = users.user_id
WHERE (user_date_accountcreated BETWEEN '2016-10-01 00:00:00' AND '2016-10-15 23:59:59') AND oauth_windowslive_id IS NOT NULL
GROUP BY year(user_date_accountcreated), month(user_date_accountcreated), day(user_date_accountcreated)


Is there any way to merge with 3 requests in only one with
COUNT(*) AS total_facebook
,
COUNT(*) AS total_google
,
COUNT(*) AS total_windowslive
?

Thanks

vkp vkp
Answer

You can do it by moving the where conditions to the count. This is called conditional aggregation.

Also when youleft join and use a where condition, it is converted to an inner join. To avoid it move the date condition on to the left join.

SELECT 
date_format(`user_date_accountcreated`, "%Y-%m-%d") AS `date`, 
COUNT(case when oauth_facebook_id IS NOT NULL then 1 end ) AS total_facebook,
COUNT(case when oauth_google_id IS NOT NULL then 1 end) AS total_google,
COUNT(case when oauth_windowslive_id IS NOT NULL then 1 end) AS total_windowslive
FROM users 
LEFT JOIN users_oauth ON users_oauth.oauth_user_id = users.user_id 
AND user_date_accountcreated BETWEEN '2016-10-01 00:00:00' AND '2016-10-15 23:59:59'
GROUP BY date_format(`user_date_accountcreated`, "%Y-%m-%d")
--year(user_date_accountcreated), month(user_date_accountcreated), day(user_date_accountcreated)
Comments