Code: Select all
SELECT
SUM(CASE WHEN signupdate = CURDATE() THEN 1 ELSE 0 END) AS numtoday,
SUM(CASE WHEN WEEK(signupdate) = WEEK(CURDATE()) THEN 1 ELSE 0 END) AS numthisweek,
SUM(CASE WHEN MONTH(signupdate) = MONTH(CURDATE()) THEN 1 ELSE 0 END) AS numthismonth,
SUM(CASE WHEN YEAR(signupdate) = YEAR(CURDATE()) THEN 1 ELSE 0 END) AS numthisyear,
SUM(CASE WHEN signupdate = CURDATE() THEN signups.amount ELSE 0 END) AS amounttoday,
SUM(CASE WHEN WEEK(signupdate) = WEEK(CURDATE()) THEN signups.amount ELSE 0 END) AS amountthisweek,
SUM(CASE WHEN MONTH(signupdate) = MONTH(CURDATE()) THEN signups.amount ELSE 0 END) AS amountthismonth,
SUM(CASE WHEN YEAR(signupdate) = YEAR(CURDATE()) THEN signups.amount ELSE 0 END) AS amountthisyear,
SUM(signups.amount) AS amountever,
COUNT(signupdate) AS allcount
FROM users WHERE EXISTS(SELECT userID FROM signups WHERE affiliateID = 1)Also, does this work to find the number of signupdates in the user table where userIDs are equal to those that match the given afilliateID ( 1) in the signups table????