mySql SQL query operation on statistical quantity

I won't say much nonsense, let's just look at the code~

select project_no,
sum(case when device_state=0 then 1 else 0 end)as offTotal ,
sum(case when device_state=1 then 1 else 0 end)as onlineTotal,
sum(1)total
from iot_d_device
group by project_no
order by project_no

Supplement: MySQL uses one SQL statement to query multiple statistical results

Mall projects will inevitably encounter the problem of users querying the number of orders in different statuses on their personal center page. Of course, this problem is not difficult. You can write a DAO layer method that takes the state as an input parameter and passes in a different state value each time to query the number of orders in the corresponding state in turn.

Today, when I was writing the H5 interface, I wanted to check in a different way, that is, to query the number of orders in multiple states through one SQL. I searched online and found the method feasible, so I tried it, and it worked as expected.

The example is as follows (the data is only used to demonstrate today's problem, and the table design is not rigorous. Please don't blame me):

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for mini_test_order
-- ----------------------------
DROP TABLE IF EXISTS `mini_test_order`;
CREATE TABLE `mini_test_order` (
 `id` int(11) NOT NULL,
 `order_no` varchar(32) DEFAULT NULL COMMENT 'Order number',
 `user_id` int(11) DEFAULT NULL COMMENT 'user id',
 `shop_id` int(11) DEFAULT NULL COMMENT 'Merchant id',
 `order_status` tinyint(1) DEFAULT NULL COMMENT 'Order status',
 `create_time` int(10) DEFAULT NULL COMMENT 'Creation time',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of mini_test_order
-- ----------------------------
INSERT INTO `mini_test_order` VALUES ('1', 'aaaaaaaaa', '11', '111', '1', '1573041313');
INSERT INTO `mini_test_order` VALUES ('2', 'bbbbbbbb', '11', '222', '1', '1573041313');
INSERT INTO `mini_test_order` VALUES ('3', 'cccccccccc', '11', '333', '2', '1573041313');
INSERT INTO `mini_test_order` VALUES ('4', 'dddddddd', '11', '222', '3', '1573041313');
INSERT INTO `mini_test_order` VALUES ('5', 'eeeeeeeeee', '11', '111', '4', '1573041313');
INSERT INTO `mini_test_order` VALUES ('6', 'ffffffffffffff', '11', '111', '3', '1573041313');
INSERT INTO `mini_test_order` VALUES ('7', 'gggggggg', '11', '222', '4', '1573041313');
INSERT INTO `mini_test_order` VALUES ('8', 'hhhhhhhhh', '11', '111', '4', '1573041313');
INSERT INTO `mini_test_order` VALUES ('9', 'iiiiiiiiiiiiiiiiiii', '11', '333', '3', '1573041313');
INSERT INTO `mini_test_order` VALUES ('10', 'jjjjjjjjjjjjjjjjjjjj', '11', '222', '1', '1573041313');

The core SQL statements are as follows:

SELECT COUNT(CASE order_status WHEN 1 THEN 1 END) AS "Status 1",COUNT(CASE order_status WHEN 2 THEN 1 END) AS "Status 2",COUNT(CASE order_status WHEN 3 THEN 1 END) AS "Status 3",COUNT(CASE order_status WHEN 4 THEN 1 END) AS "Status 4" FROM `mini_test_order`;

or as follows:

SELECT COUNT(CASE WHEN order_status = 1 THEN 1 END) AS "Status 1",COUNT(CASE WHEN order_status = 2 THEN 1 END) AS "Status 2",COUNT(CASE WHEN order_status = 3 THEN 1 END) AS "Status 3",COUNT(CASE WHEN order_status = 4 THEN 1 END) AS "Status 4" FROM `mini_test_order`;

Of course, SQL statements are not limited to the two methods mentioned above. Those who like to explore are welcome to leave a message to supplement.

There are two syntaxes for MySQL case when

1. Simple functions

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END

2. Search function

CASE WHEN [expr] THEN [result1]…ELSE [default] END

The difference between the two

The former enumerates all possible values of the col_name field when the value meets the condition value1;

The latter can write judgments, and the search function will only return the first value that meets the conditions, and other cases are ignored.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.

You may also be interested in:

Detailed explanation of MySQL persistent statistics
A brief analysis of MySQL cardinality statistics
Mysql example of converting birth date into age and grouping and counting the number of people
Sample code for Python counting MySQL data volume changes and calling interface alarms
Reasons why MySQL 8.0 statistics are inaccurate
Analysis of the usage of process control functions/statistical functions/grouping queries in MySql

<<: CSS to implement sprites and font icons

>>: Linux disk space release problem summary