Jits Jits - 4 months ago 8
MySQL Question

Get sub total of two column for particular criterion and another total as full group by X parameter

I want to group by keyword name, get sum of cf1+cf2 (where bug_status=CLOSED or RESOLVED) and get total sum (irrespective of bug status). Output will have 3 columns like mentioned.

Tried query but no luck:



SELECT keyworddefs.name as keyword, IFNULL(SUM(bugs.cf1 + bugs.cf2),0) as completed, (SELECT IFNULL(SUM(bugs.cf1 + bugs.cf2) ,0) FROM bugs, keywords, keyworddefs WHERE (keywords.bug_id = bugs .bug_id) AND (keyworddefs.id=keywords.keywordid) AND (keyworddefs.name LIKE 'K%')) as total FROM bugs, keywords, keyworddefs WHERE (keywords.bug_id = bugs .bug_id) AND (keyworddefs.id=keywords.keywordid) AND (bugs.bug_status = 'VERIFIED' OR bugs.bug_status = 'CLOSED') GROUP BY keyworddefs.name DESC;


Here's the query formatted.

SELECT keyworddefs.name as keyword,
IFNULL(SUM(bugs.cf1 + bugs.cf2),0) as completed,
(SELECT IFNULL(SUM(bugs.cf1 + bugs.cf2) ,0)
FROM bugs, keywords, keyworddefs
WHERE (keywords.bug_id = bugs .bug_id)
AND (keyworddefs.id=keywords.keywordid)
AND (keyworddefs.name LIKE 'K%')) as total
FROM bugs, keywords, keyworddefs
WHERE (keywords.bug_id = bugs .bug_id)
AND (keyworddefs.id=keywords.keywordid)
AND (bugs.bug_status = 'VERIFIED' OR bugs.bug_status = 'CLOSED')
GROUP BY keyworddefs.name DESC;


SQL Fiddle:



http://sqlfiddle.com/#!9/a11b4/7

Expected:



Matching records:
cf1+cf2 bugid, keyword bug_status
5 (102, 'K1') CLOSED
3 (565, 'K2') CLOSED
3 (1352, 'K1') VERIFIED
4 (13634, 'K1') NEW

# Query output should be:
keyword completed total
K1 8 12
K2 3 3


DDLs:

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
(102, '2016-07-19', 2, 1, 'CLOSED'),
(72123, '2016-07-19', 2, 1, 'VERIFIED'),
(57234, '2016-07-19', 2, 1, 'VERIFIED'),
(1352, '2016-07-19', 2, 1, 'VERIFIED'),
(565, '2016-07-19', 2, 1, 'CLOSED'),
(13634, '2016-07-22', 2, 2, 'NEW');


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
(102, '3'),
(565, '4'),
(398, '1'),
(565, '2'),
(1352, '1'),
(57234, '2'),
(1363, '1'),
(72123, '2'),
(13634, '3');


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, 'K1', 'My K1 item'),
(4, 'K2', 'My K2 item');


Please let me know if anything went wrong in terms of copying and formatting sample data.

Can someone please help me to get output like expected?

Answer

It looks to me like you're making this too complicated.

For one thing, you should join your keywords and keyworddefs tables ON keywords.keywordid = keyworddefs.name. You're using keyworddefs.id. That's a number. So, your old-timey early 1990s comma join yields no results.

For another thing, you don't need to join the keyworddefs table to get your result.

SUM() operations rarely yield NULL results. So, you should put your conditionals inside the parentheses of SUM() rather than outside.

Finally, you need a GROUP BY operation with two SUM() aggregates in it. One should be conditioned on the bug_status and the other should not.

http://sqlfiddle.com/#!9/a11b4/11/0

Something like this should work.

SELECT keywords.keywordid, 
       SUM(CASE WHEN bugs.bug_status IN ('CLOSED', 'RESOLVED')
                THEN bugs.cf1 + bugs.cf2
                ELSE 0 END)                                   completed,
       SUM(bugs.cf1 + bugs.cf2)                               total
  FROM bugs
  JOIN keywords ON bugs.bug_id = keywords.bug_id
 GROUP BY keywords.keywordid
 ORDER BY keywords.keywordid

If you need to filter your results by keywords.keywordid LIKE 'K%', you can just add a where clause.