Counting all values, a sum between one range and a count in another

Trying to run a query that does three things. I want it to:

1. Get the amount each user earned in the month they completed their job (DateCompleted)
2. Count all of the jobs in a database if they were submitted in the current month (Submitted)
3. Count all jobs for the current user if they were submitted in the current month (Submitted)

I have the following

SELECT

SUM(case when j.Progress = 5 and (DateCompleted between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() ) then j.Amount else 0 end) as Earned,

(SELECT COUNT(*) FROM Jobs WHERE (Submitted between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )) as AllJobs,

COUNT(case when (Submitted between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() ) then 1 else 0 end) as UserJobs

FROM Jobs j
LEFT JOIN Staff s
ON s.StaffID = j.StaffID
WHERE j.StaffID = s.StaffID
AND StaffID = '631613'
LIMIT 1

Whilst Earned and AllJobs are returning the correct values, UserJobs is showing jobs from outside of this date range.

Answer

I suspect that the conditional COUNT() should be a SUM():

sum(case when Submitted between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() then 1 else 0 end) as UserJobs

Rationale: COUNT() takes in account all non-null values; so no matter if the conditional expression within returns 0 or 1, it is still counted in, which, likely, is not what you want.

Note that, since, you are using MySQL, you could furthermore simplify that:

SUM(Submitted between DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()) as UserJobs

Other issues or possible improvements with your query:

• StaffID is ambiguous in the where clause since this column exists in both tables being joined

• limit 1 is superfluous in this aggregate query that has no group by clause (it is guaranteed to return either 0 or 1 row)

• nested parentheses around conditions are unnecessary

• it should be possible to use conditional aggregation all the way rather than a subquery to compute AllJobs, by moving the filtering on StaffID from the where clause to the conditions inside the aggregate functions – but this is hard to assess without seeing actual table structures and data