title: MySQL - Return count() of column even if 0 results
body: <p>I have 2 tables and I'm trying to write a report query where I get a count for all tasks with a given status. I want a count for all task statuses, even if there are no tasks with that task status(meaning it should return a row of 0 instead of nothing for that status). I've tried the following:</p>
  
  <pre><code>     select t.TaskPriorityCode as priority,
  count(tp.code) as count
     from task t
      LEFT JOIN priority tp on tp.code = t.TaskPriorityCode
         where t.companyId = 16
     and t.projectId = 4
      group by t.TaskPriorityCode
  </code></pre>
  
  <p>which returns: </p>
  
  <pre><code>CRITICAL    4
  HIGH    1
  LOW 1
  </code></pre>
  
  <p>But as you can see, the task status of MEDIUM does not appear because it has no records. I would like this query to return this:</p>
  
  <pre><code>CRITICAL    4
  HIGH    1
  LOW 1
  MEDIUM 0
  </code></pre>
  
  <p>Below is some SQL to give you a snapshot of what I was tinkering in dev with as well as do the inserts. </p>
  
  <pre><code>CREATE TABLE `priority` (
    `code` varchar(20) NOT NULL,
    `display` varchar(255) NOT NULL,
    `description` varchar(255) DEFAULT NULL,
    `metavalue` varchar(255) DEFAULT NULL,
    `createdAt` datetime NOT NULL,
    `updatedAt` datetime NOT NULL
  ) 
  
  INSERT INTO `priority` VALUES ('CRITICAL','Critical','Critical',NULL,'2018-04-08 18:36:51','2018-04-08 18:36:51'),('HIGH','High','High',NULL,'2018-04-08 18:36:51','2018-04-08 18:36:51'),('LOW','Low','Low',NULL,'2018-04-08 18:36:51','2018-04-08 18:36:51'),('MEDIUM','Medium','Medium',NULL,'2018-04-08 18:36:51','2018-04-08 18:36:51');
  
  CREATE TABLE `task` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `taskName` varchar(255) NOT NULL,
    `description` varchar(255) DEFAULT NULL,
    `startDt` datetime NOT NULL,
    `finishDt` datetime DEFAULT NULL,
    `estimatedHrs` float DEFAULT NULL,
    `completedHrs` float DEFAULT NULL,
    `active` tinyint(1) NOT NULL DEFAULT '1',
    `repetitiveDays` varchar(255) DEFAULT NULL,
    `createdAt` datetime NOT NULL,
    `submittedDt` datetime DEFAULT NULL,
    `closedDt` datetime DEFAULT NULL,
    `updatedAt` datetime NOT NULL,
    `deletedAt` datetime DEFAULT NULL,
    `assignorId` int(11) DEFAULT NULL,
    `assigneeId` int(11) DEFAULT NULL,
    `projectId` int(11) NOT NULL,
    `TaskStatusCode` varchar(20) NOT NULL,
    `TaskPriorityCode` varchar(20) NOT NULL,
    `companyId` int(11) NOT NULL
  )
  
  
  INSERT INTO `task` VALUES (8,'My First Task  - Fix errors','My First Task  - Fix errors Description','2018-01-01 05:00:00','2018-01-01 05:00:00',100,50,1,'wed,sat,sun','2018-04-16 14:35:02','2018-04-16 15:06:56','2018-04-16 15:23:35','2018-05-13 23:43:56',NULL,13,13,4,'IN_PROGRESS','CRITICAL',16),(9,'Second Task - again fix errors','Second Task - again fix errors','2018-01-01 05:00:00','2018-05-01 04:00:00',50,10,1,'','2018-04-16 15:35:52',NULL,NULL,'2018-04-16 15:36:57',NULL,13,13,5,'IN_PROGRESS','MEDIUM',16),(10,'Third Task - again fix errors','Third Task - again fix errors','2018-04-01 04:00:00','2018-04-01 04:00:00',100,100,1,'','2018-04-16 15:36:36','2018-04-19 16:07:14','2018-04-19 16:07:36','2018-04-19 16:07:36',NULL,13,13,4,'CLOSED','LOW',16),(11,'Fourth Task - again fix errors','Third Task - again fix errors','2018-01-01 05:00:00','2018-05-01 04:00:00',200,0,1,'','2018-04-16 15:37:54',NULL,NULL,'2018-04-16 15:38:32',NULL,13,13,4,'ASSIGNED','HIGH',16),(12,'Fifth Task - again fix errors','Fifth Task - again fix errors','2018-01-01 05:00:00','2018-06-01 04:00:00',22,NULL,1,'','2018-04-16 15:39:49',NULL,NULL,'2018-04-16 15:39:49',NULL,13,13,4,'ASSIGNED','CRITICAL',16),(13,'Fix payment',NULL,'2018-05-04 04:00:00','2018-05-11 04:00:00',40,NULL,0,'','2018-05-04 14:50:31',NULL,NULL,'2018-05-04 14:53:34','2018-05-04 14:53:34',15,15,4,'ASSIGNED','CRITICAL',16),(14,'New','new','2018-05-01 04:00:00','0000-00-00 00:00:00',20,NULL,1,'','2018-05-13 23:44:50',NULL,NULL,'2018-05-13 23:44:50',NULL,13,13,4,'ASSIGNED','CRITICAL',16);
  </code></pre>