CODEWITHSUNDEEP
mysql
santa
santa

Reputation: 12512

Get number of projects and average completion rate

I have a table with some project data. One column contains "Completion %". By default the value is set to 0. What is the best way to structure the mySQL query and write a code to get the following 3 values:

I don't think I can do it all in a single query...

Thanks

Upvotes: 1

Views: 116

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562498

SELECT project_id,
  COUNT(*) AS total_users,
  COUNT(IF(completion != 0, rowID, NULL)) AS participants,
  AVG(IF(completion != 0, completion, NULL)) AS average_completion_rate
FROM Projects
WHERE project_id = 5
GROUP BY project_id;

It would be helpful in the future if you run SHOW CREATE TABLE of your table and include it in your question.

Re comment: So it sounds like your table is something like this:

CREATE TABLE Projects (
  rowID         INT PRIMARY KEY,
  project_id    INT,
  completion    TINYINT
);

I have edited my suggested query above to match.

Upvotes: 1

Related Questions