How to construct SQL query to get the output as below-CodePudding

the below image is how the table related and the expected output result.

I used MySQL version 6.3.3. and the below is currently can be achieved.

and below is the SQL query code

select table2.Status as StatusName, count(table1.Status) as NoOfStatus from testingdb.table1, testingdb.table2 where table1.Status=table2.Status group by table1.Status;

Anyone can help me to improvise the SQL query code above to get the expected output is very appreciated and thank you very much...

CodePudding user response：

you can use ROLLUP functionality

CREATE TABLE table_a (
  item varchar(20),
  item_code varchar(20),
  item_status varchar(1)
);

CREATE TABLE table_b (
  item_status varchar(1),
  item_status_name varchar(200)
);

INSERT INTO table_a VALUES ('101', 'SEQ', 'A'),
('202', 'FEQ', 'A'),
('303', 'AEQ', 'C'),
('404', 'BEQ', 'B'),
('505', 'CEQ', 'B'),
('606A', 'BEQ', 'B'),
('505B', 'CEQ', 'B'),
('606', 'GEQ', 'D'),
('707', 'HEQ', 'E');


INSERT INTO table_b VALUES ('A', 'Completed'),('B', 'Half-Completed'),('C', 'Started'),('D', 'Negotiated'),('E', 'OnProposal');

And here is the super simple query:

SELECT b.item_status_name as status_name
, count(item) as nbr_of_progress
FROM table_a a 
LEFT JOIN table_b b 
ON b.item_status = a.item_status
GROUP BY b.item_status_name WITH ROLLUP

see the live demo