Given this two tables:

CREATE TABLE `file` (
  `id` int(11) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `is_base_found` tinyint(1) NOT NULL DEFAULT 0,
  `is_target_found` tinyint(1) NOT NULL DEFAULT 0,
  `last_run_date` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

and

CREATE TABLE `statistics` (
  `id` int(11) NOT NULL,
  `file_id` int(11) NOT NULL,
  `status` varchar(8) NOT NULL utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

where file_id is reference from file table. My statistics table has this following data:

Using mysql query how can I get the count of status passed, count of status failed, count of file_id in One Query(If not possible, what is the best way to achieve)? As of now, what I am doing is like querying per status:

SELECT f.*, COUNT(s.status)
  FROM file f
 INNER JOIN statistics s
    ON f.id = s.file_id
 WHERE r.status = "passed"
   AND f.file_name LIKE ?
 GROUP BY c.file_name

My Desired output is something like this:

Any help is very much appreciated!

CodePudding user response：

You seem to need a conditional aggregation such as

SELECT c.file_name, 
       SUM(status='passed') AS `# Passed`, -- conditional counts
       SUM(status='failed') AS `# Failed`,
       COUNT(*) AS `# Run`                 -- total count
  FROM file f
  JOIN statistics s
    ON f.id = s.file_id
 GROUP BY c.file_name