Search code examples
sqlmysqlgroup-by

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

Solution

  • 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