Question 180791 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Count 2 different date fields in a single query

I have 2 different date fields, 1 when the case was created and the other when the case completed. I am trying to count total cases and total completions in say 2023.

Whenever I run my query and set the where field, both columns return the same figure.

SELECT COUNT(*) AS cases,
       COUNT(IF(YEAR(tbl_lead.CompDate) = '2023', 1, NULL)) AS Completed,
       tbl_lead.LeadSource
FROM tbl_lead
WHERE YEAR(tbl_lead.CompDate) = '2023'
GROUP BY tbl_lead.LeadSource

I guess what I’m trying to do is count all records for 2023 as cases, then count how many of those have completed in 2023. Is it impossible?

It should output as:

Cases Completed LeadSource
1000 500 Google
2000 700 Facebook

Whereas it currently outputs:

Cases Completed LeadSource
500 500 Google
700 700 Facebook

Thank you

Tags:

3

Answers


  1. 0

    With this where condition, all the rows in the query are of cases completed in 2023 (i.e, match the condition in the if), so both counts will return the same result. Drop the where clause and you should be OK:

    Select
    count(*) As cases,
    Count(If(Year(tbl_lead.CompDate) = '2023', 1, Null)) As Completed,
    tbl_lead.LeadSource
From
    tbl_lead
Group By
    tbl_lead.LeadSource
    Login or Signup to reply.
  2. 0

    Your current query only refers to CompDate but in your question you refer to two different date columns. Is this what you are looking for:

    SELECT SUM(CreateDate >= '2023-01-01' AND CreateDate < '2024-01-01') AS cases,
       SUM(CompDate >= '2023-01-01' AND CompDate < '2024-01-01') AS Completed,
       LeadSource
FROM tbl_lead
WHERE (CreateDate >= '2023-01-01' AND CreateDate < '2024-01-01')
   OR (CompDate >= '2023-01-01' AND CompDate < '2024-01-01')
GROUP BY LeadSource
    Login or Signup to reply.
  3. 0

    I think there is a column (lets call it is_completed) that indicates whether a case is completed or not, in this case, try this:

    SELECT COUNT(*) AS cases,
       sum(is_completed) AS Completed,
       tbl_lead.LeadSource
FROM tbl_lead
WHERE YEAR(tbl_lead.CompDate) = '2023'
GROUP BY tbl_lead.LeadSource
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search