how to get the desired result based on some certain condition in sql using case?

Question

I have a table which traces the users records I want to know which are the complete and process users's records based on their status

Here is the sql query

SELECT users.UserID,users.UserName,users.FirstName,users.LastName,users.Email,
CASE WHEN inword.inword_status = '3' THEN  count(*) END As 'Process' ,
CASE WHEN inword.inword_status = '4' THEN  count(*) END AS 'Complete' 
FROM tbl_user users
INNER JOIN tbl_inword inword on users.UserID=inword.UserID 
Where inword.Status=1 and users.Status=1 and 
inword.CreatedDate BETWEEN '2020-10-01' and '2020-10-31' and inword.inword_status in (3)
group by  users.UserID

Here is Query Output

My Expected result is

UserID  Name      Total    Process     Complete
1       Umair     1           1           0
1       Basit     20          20          0
1       Zaidi     34          32          2

Any Help would be Appreciated

Answer 1

You're not doing your conditional aggregation correctly, you should use something like:

COUNT(CASE WHEN inword.inword_status = '3' THEN inword.UserId END) As 'Process' ,
COUNT(CASE WHEN inword.inword_status = '4' THEN inword.UserId END) AS 'Complete' 

Or you can take advantage of MySQL treating booleans as 1 or 0 in a numeric context and simplify to:

SUM(inword.inword_status = '3') As 'Process' ,
SUM(inword.inword_status = '4') AS 'Complete'