CODEWITHSUNDEEP
mysql
Jitesh Sojitra
Jitesh Sojitra

Reputation: 4043

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.

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      |
+------------------------+

Upvotes: 0

Views: 45

Answers (1)

Drew
Drew

Reputation: 24960

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 
 ) 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.

Upvotes: 1

Related Questions