Ranks of the conversion packet statistics

Ask
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`id` int(11) NOT NULL auto_increment COMMENT 'The key ID',
`c_id` int(11) NOT NULL COMMENT 'CID',
`name` varchar(128) NOT NULL COMMENT 'Market',
`status` int(11) NOT NULL COMMENT 'Statistics type, 1- type, 2-B type',
`oper_status` int(11) default NULL COMMENT '1- 2- invalid 3- open; closed;',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES ('1', '17', 'name', '1', '1');
INSERT INTO `result` VALUES ('2', '18', 'name', '1', '1');
INSERT INTO `result` VALUES ('3', '18', 'name', '2', '1');
INSERT INTO `result` VALUES ('4', '19', 'name', '1', '1');
INSERT INTO `result` VALUES ('5', '19', 'name', '2', '2');
INSERT INTO `result` VALUES ('6', '19', 'name', '2', '3');
INSERT INTO `result` VALUES ('6', '20', 'name', '2', '3');

Hope to get results
According to the c_id packet statistics, status is the number of 1 or 2, and oper_status=1, and c_id from a LIST (not a fixed length)
c_id count(status=1) count(status=2)
17 1 0
18 1 1
19 1 0

Started by Cary at December 11, 2016 - 8:49 PM

SELECT
c_id,
sum(if(status=1,1,0)) as count(if(status=2,1,0)),
sum(status=2) as count(status=2)
from result group by c_id

Posted by Reed at December 20, 2016 - 9:23 PM

Wrong. Should
SELECT
c_id,
sum(if(status=1,1,0)) as count(status=1),
sum(if(status=2,1,0)) as count(status=2)
from result group by c_id
Sorry!

Posted by Reed at December 29, 2016 - 9:30 PM


MySQL cross table
A cross table in some databases, but in MySQL do not have this function, but the online to see a lot of friends to find out a solution, especially the brainstorming. The following is the whole understanding method: sample data: create table tx( id int primary key, c1 c...

Posted by Blair at January 02, 2017 - 10:03 PM