Jits Jits - 4 months ago 10
MySQL Question

Extend given long mysql query and get count of completed and not completed

When i write below MySQL query, it gives me correct output like mentioned after that i want to get completed and not completed count. I already tried but failed so your suggestion would be appreciated and will try now in MySQL editor.

SQL Fiddle:



http://sqlfiddle.com/#!9/c8dab/1

Question:



How can i get completed and not completed count by extending below query?


Query:



SELECT keyworddefs.name as sprint,
SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED')
THEN bugs.cf1 + bugs.cf2
ELSE 0 END) completed,
SUM(bugs.cf1 + bugs.cf2) total,
(CASE WHEN SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED')
THEN bugs.cf1 + bugs.cf2
ELSE 0 END)=SUM(bugs.cf1 + bugs.cf2)
THEN 'Completed'
ELSE 'Not Completed' END) status
FROM bugs
JOIN keywords ON bugs.bug_id = keywords.bug_id
JOIN keyworddefs ON keyworddefs.id = keywords.keywordid
GROUP BY keywords.keywordid
ORDER BY keyworddefs.name DESC;


Output:



+--------+------------------------+--------------------+---------------+
| name | completed | total | status |
+--------+------------------------+--------------------+---------------+
| K2 | 14 | 14 | Completed |
| J2 | 16 | 24 | Not Completed |
| J1 | 0 | 5 | Not Completed |
+--------+------------------------+--------------------+---------------+


After extending query, expected output just:



+---------------+--------+
| status | count |
+------------------------+
| Completed | 1 |
| Not Completed | 2 |
+------------------------+

Answer

As Strawberry said, give it a derived table name.

Schema:

-- bugs table1 (master table) :

CREATE TABLE `bugs` 
(   `bug_id` int(11) NOT NULL, 
    `bug_date` date NOT NULL, 
    `cf1` int(11) NOT NULL, 
    `cf2` int(11) NOT NULL, 
    `bug_status` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bugs` (`bug_id`, `bug_date`, `cf1`, `cf2`, `bug_status`) VALUES 
(101, '2016-07-19', 3, 2, 'RESOLVED'), 
(102, '2016-07-19', 2, 1, 'VERIFIED'), 
(103, '2016-07-19', 2, 1, 'VERIFIED'), 
(103, '2016-07-19', 2, 1, 'VERIFIED'), 
(1363, '2016-07-19', 2, 1, 'VERIFIED'), 
(1352, '2016-07-19', 2, 1, 'VERIFIED'), 
(102, '2016-07-19', 2, 1, 'VERIFIED'), 
(102, '2016-07-22', 2, 2, 'CLOSED'), 
(103, '2016-07-22', 2, 2, 'CLOSED'), 
(103, '2016-07-22', 2, 2, 'CLOSED'), 
(102, '2016-07-19', 3, 2, 'NEW'), 
(102, '2016-07-19', 2, 1, 'REOPENED'), 
(102, '2016-07-19', 2, 1, 'CLOSED'), 
(102, '2016-07-19', 2, 1, 'VERIFIED'), 
(1363, '2016-07-19', 2, 1, 'VERIFIED'), 
(1352, '2016-07-19', 2, 1, 'VERIFIED'), 
(565, '2016-07-19', 2, 1, 'VERIFIED'), 
(398, '2016-07-22', 2, 2, 'CLOSED'), 
(565, '2016-07-22', 2, 2, 'CLOSED'), 
(9872, '2016-07-22', 2, 2, 'CLOSED');

-- keywords table2 (having keyword ids):

CREATE TABLE `keywords` 
(   `bug_id` int(11) NOT NULL, 
    `keywordid` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `keywords` (`bug_id`, `keywordid`) VALUES 
(101, '1'), (102, '2'), (103, '3'), (104, '4'), (105, '1'), (106, '1'), (107, '2'), (108, '3'), (109, '4');

-- keyworddefs table3 (having keyword names according to keywordid):

CREATE TABLE `keyworddefs` 
(   `id` int(11) NOT NULL, 
    `name` varchar(200) NOT NULL,
    `description` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `keyworddefs` (`id`, `name`, `description`) VALUES 
(1, 'J1', 'My J1 item'), (2, 'J2', 'My J2 item'), (3, 'K2', 'My K2 item'), (4, 'K2', 'My K2 item');

Your query:

SELECT keyworddefs.name as sprint,  
       SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED') 
                 THEN bugs.cf1 + bugs.cf2 
                 ELSE 0 END) completed_story_points, 
       SUM(bugs.cf1 + bugs.cf2) total_story_points, 
       (CASE WHEN SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED') 
                 THEN bugs.cf1 + bugs.cf2 
                 ELSE 0 END)=SUM(bugs.cf1 + bugs.cf2) 
                 THEN 'Completed' 
                 ELSE 'Not Completed' END) sprint_status 
  FROM bugs 
  JOIN keywords ON bugs.bug_id = keywords.bug_id 
  JOIN keyworddefs ON keyworddefs.id = keywords.keywordid 
 GROUP BY keywords.keywordid 
 ORDER BY keyworddefs.name DESC; 

Your Output:

+--------+------------------------+--------------------+---------------+
| sprint | completed_story_points | total_story_points | sprint_status |
+--------+------------------------+--------------------+---------------+
| K2     |                     14 |                 14 | Completed     |
| J2     |                     16 |                 24 | Not Completed |
| J1     |                      0 |                  5 | Not Completed |
+--------+------------------------+--------------------+---------------+

Wanted:

+---------------+--------+
|   status      | count  |
+------------------------+
| Completed     | 1      |
| Not Completed | 2      |
+------------------------+

Lazy approach (meaning, without much thought):

SELECT sprint_status,count(*) AS count 
FROM 
( 
 SELECT keyworddefs.name as sprint,   
       SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED') 
                 THEN bugs.cf1 + bugs.cf2 
                 ELSE 0 END) completed_story_points, 
       SUM(bugs.cf1 + bugs.cf2) total_story_points, 
       (CASE WHEN SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'VERIFIED') 
                 THEN bugs.cf1 + bugs.cf2 
                 ELSE 0 END)=SUM(bugs.cf1 + bugs.cf2) 
                 THEN 'Completed' 
                 ELSE 'Not Completed' END) sprint_status 
  FROM bugs 
  JOIN keywords ON bugs.bug_id = keywords.bug_id 
  JOIN keyworddefs ON keyworddefs.id = keywords.keywordid 
 GROUP BY keywords.keywordid 
 ORDER BY keyworddefs.name DESC 
 ) xDerived  
 GROUP BY sprint_status 
 ORDER BY sprint_status;  

Output:

+---------------+-------+
| sprint_status | count |
+---------------+-------+
| Completed     |     1 |
| Not Completed |     2 |
+---------------+-------+

Order by whatever

Every derived table requires a name. The above wrapped chunk is one. So we chose the name xDerived. It is not used by name thereafter (though it could have been). But it still needs a name or an Error will occur.