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

Count 2 different date fields in a single query
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

