1

I have simple MySQL Query:

SELECT who, status, 
COUNT(status) AS Total, completion_status AS Role
FROM `trespondent_360` 
GROUP BY who, status 
ORDER BY who, status

This correctly brings back to the total in the database for each person. I need to then count the Total where their completion_status ='s either Started or Complete.

The output should be something like:

Who          Total          Status          Completed
Mike Smith   1              Self            1
Mike Smith   5              Other           2
Bob Jones    1              Self            0
Bob Jones    6              Other           5

Whenever I add to the above code, I get the error that the Operand should contain 1 column(s). See below

SELECT who, status, 
COUNT(status) AS Total, completion_status AS Role,
(SELECT who, status, COUNT(status) FROM `trespondent_360` 
  WHERE completion_status IN ('Started','Complete')) AS Completed
FROM `trespondent_360` 
GROUP BY who, status 
ORDER BY who, status

Any advice and feedback on what I need to do would be gratefully received.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Homer_J
  • 3,277
  • 12
  • 45
  • 65

1 Answers1

1

If you want to count status Complete or Started you can use CASE WHEN:

SELECT who, status, 
       completion_status AS Role,
       COUNT(status) AS Total,
       COUNT(CASE WHEN status IN ('Started','Complete') THEN 1 END) AS Completed
FROM `trespondent_360` 
GROUP BY who, status 
ORDER BY who, status;

By the way you should use aggregation function on completion_status column, I know it works with MySQL but it is not ANSI compliant. More info why you should do it

Community
  • 1
  • 1
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275