Is it possible to COUNT if one value occured more than the other value in a column using SQL

I have this table called task_status which has the following structure:

CREATE TABLE `task_status` (
  `task_status_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  `date_recorded` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `task_status`
  ADD PRIMARY KEY (`task_status_id`);

ALTER TABLE `task_status`
  MODIFY `task_status_id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

INSERT INTO `task_status` (`task_status_id`, `status_id`, `task_id`, `date_recorded`) VALUES
(1, 1, 16, 'Wednesday 6th of January 2021 09:20:35 AM'),
(2, 2, 17, 'Wednesday 6th of January 2021 09:20:35 AM'),
(3, 3, 18, 'Wednesday 6th of January 2021 09:20:36 AM');

and a status_list table that has the possible statuses available

CREATE TABLE `status` (
  `statuses_id` int(11) NOT NULL,
  `status` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `status`
  ADD PRIMARY KEY (`statuses_id`);

ALTER TABLE `status`
  MODIFY `statuses_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

INSERT INTO `status` (`statuses_id`, `status`) VALUES
(1, 'Yes'),
(2, 'Inprogress'),
(3, 'No');

Now what I want to do is check which number occurred more inside the status_id column 1 occurred more, 2 occurred more or 3 occurred more? using SQL.

Is it possible to do and if so how to?

Answer

You can count the column first then filter with max
there is a lot of different way to do this but i prefer using cte. Here is a example :

with cte as(
select status_id,count(*) cnt from task_status 
group by status_id
)
select * from cte
where cnt = (select max(cnt) from cte)

also here is db<>fiddle for better examine.
I modify some data to show the much more understandable output. But idea is same.

also I don’t really think status table have any work doing here, but remind me if I misunderstand what you mean.

Advertisement

Answer