Mysql query to get multiple counts of fields in a table-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

First of all, do not expect exact query as answer mate! We all are here to learn and share experience.
If you really want to have answers as query, you should provide query to reproduce to issue :)

For create 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');

Not exact solution, but you can take this as starter, do further work at your end.

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;