CODEWITHSUNDEEP
phpmysqlsqlgroup-bycount
BN83
BN83

Reputation: 912

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.

Upvotes: 0

Views: 39

Answers (1)

GMB
GMB

Reputation: 222582

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

Upvotes: 1

Related Questions