0

The goal is to get the progress on a list of tasks considering the status of each tasks, ruling out invalid tasks.

Currently, I managed to do this at a very expensive hit on performance and would like to know if it's possible to achieve the same result in one SQL sentence or at least find a more optimized way.

SELECT COUNT(`id`) AS `status` FROM `task` WHERE `status` = 'done'
UNION
SELECT COUNT(`id`) AS `status` FROM `task` WHERE `status` = 'pending';

The above query results in 2 rows, I can sum them up and get the total of 5 which is correct, first row is count of done tasks: from there is easy to get 3/5 or 60% done

status
------
3
2

I know this example is very specific, I tried this query but encountered some problems. Is about the grouping? or I'm completely wrong here?

SELECT
COUNT(CASE WHEN `status` = 'done' THEN 1 ELSE 0 END) AS `done`,
COUNT(CASE WHEN `status` = 'pending' THEN 1 ELSE 0 END) AS `pending`
FROM `task`
GROUP BY `status`;

The above query results in 2 columns and 3 repeated rows:

done    pending
---------------
3       3
1       1
2       2

At this example table I'm looking at 3 out of 5 valid tasks or 60% completed.

name        status
------------------
task 1      done
task 2      done
task 3      pending
task 4      done
task 5      invalid
task 6      pending

Thanks for any help!

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
gustyaquino
  • 767
  • 1
  • 7
  • 28

1 Answers1

1

You were counting the false condition as well with else 0. Eliminating it would default to null for a comparison that is false and wouldn't be counted.

SELECT
COUNT(CASE WHEN `status` = 'done' THEN 1 END) AS `done`,
COUNT(CASE WHEN `status` = 'pending' THEN 1 END) AS `pending`
FROM `task`
WHERE `status` in ('done','pending')

Or this can be simplified to 

SELECT
SUM(`status` = 'done') AS `done`,
SUM(`status` = 'pending') AS `pending`
FROM `task`
WHERE `status` in ('done','pending')
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58