Mysql query to get multiple counts of fields in a table

user_17682616 0
Viewed 42

table name: testing

Create query (credit to @ankit.jbp for helping out in the same):

For creating table

CREATE TABLE testing   
( id int(11) NOT NULL,  
sub_id int(11) NOT NULL,  
status varchar(50) NOT NULL )  
ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO testing   
(id, sub_id, status)   
VALUES (1, 1, 'NOT STARTED'), (1, 1, 'NOT STARTED'), (1, 1, 'NOT STARTED'), (1, 2, 'COMPLETE'), (1, 2, 'COMPLETE'), (2, 3, 'COMPLETE'), (2, 3, 'DEFAULT'), (3, 4, 'NOT STARTED');
id sub_id status
1 1 NOT_STARTED
1 1 NOT_STARTED
1 1 NOT_STARTED
1 2 COMPLETE
1 2 COMPLETE
2 3 COMPLETE
2 3 DEFAULT
3 4 NOT_STARTED

The id and sub_id are unique in their fields. There may be multiple sub_ids in 1 id. I want to create an MySQL Query to tell me the counts of status ='COMPLETE' and total of both id and sub_id Group By sub_id and other information in the following desired output:

total count of id id sub_id total count of sub_id total count of sub_id with status='COMPLETE'
5 1 1 3 0
5 1 2 2 2
2 2 3 2 1
1 3 4 1 0

I would appreciate the help in this query, thank you.

mysql sql
2 Answers
select status, count(status)
from table 
group by status
order by status

the output should like

NOT_STARTED,4
COMPLETE,3
DEFAULT,1

aditional you can add a sum to have a total

user_166004110

It would help, if you could provide what you have tried already. Here are some suggestions to start with:

For creating the table:

CREATE TABLE `testing` (
  `id` int(11) NOT NULL,
  `sub_id` int(11) NOT NULL,
  `status` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `testing` (`id`, `sub_id`, `status`) VALUES
(1, 1, 'NOT STARTED'),
(1, 1, 'NOT STARTED'),
(1, 1, 'NOT STARTED'),
(1, 2, 'COMPLETE'),
(1, 2, 'COMPLETE'),
(2, 3, 'COMPLETE'),
(2, 3, 'DEFAULT'),
(3, 4, 'NOT STARTED');

Those are no exact solutions, but you can take them as a starter.

select id,sub_id, status, count(id) from (SELECT * FROM `testing` where 
status !='COMPLETE') t union Select id,sub_id, status,count(id) from 
(SELECT * FROM `testing` where status ='COMPLETE') t2;
user_20585820
Related