Replace count query with sum in DB2

In DB2, the sum function used to replace the count query is to calculate and sum the column values. The www.2cto.com count function accumulates the columns that meet the conditions and adds one when the conditions are met. The count function is commonly used to count the number of records meeting a certain condition. For example, count the number of boys in student in the student information table: [SQL] select count (*) from student where sex = 'M' common sum function is used to sum the data that meets the condition. For example, calculate the total score of 'Scott 'in the score of the student's score table: [SQL] select sum (achv) from score where stu_name = 'Scott 'and stu_id = '123' except for counting the rows in all tables, in other cases, the count can be replaced by sum. For example, the number of boys in the preceding statistics can be written as follows: [SQL] select sum (case when sex = 'M' then 1 else 0 end) from student Example 2: count the number of all employees in the employee list and the number of male employees [SQL] select 'all', count (*) from employee union all select 'man ', count (*) from employee where sex = 'M' 1 2 --- ----------- man 23 all 42 the following uses the sum function to replace count male employees: [SQL] select 'man ', sum (case when sex = 'M' then 1 else 0 end) from employee union all select 'all', count (*) from employee 1 2 --- ----------- All 42 man 23 what if I want to count all male and female, and all employees? If you use the count function, you need to write three queries and then union them all to get our data: [SQL] select 'man ', count (*) from employee where sex = 'M' union all select 'femail', count (*) from employee where sex = 'F' union all select 'all', count (*) from employee 1 2 ------ ----------- man 23 femail 19 all 42 this query implementation, needs to read three tables, the efficiency is naturally low, then why we do not read the table once, all statistics? When a table is read to count all the data, are the results of count (*), count (column_name), count (0), count (null), and so on the same? Count (*) is used to count the number of rows that meet the condition. count (0) or count (1) is used to count the number of rows that meet the condition, which is the same as count; count (column_name) is different. It filters out the value of column is null. [SQL] select count (case when 1 = 0 then 1 else null end) cnt1, count (case when 1 = 1 then 1 else null end) cnt2 from sysibm. sysdummy1 CNT1 CNT2 ----------- 0 1 Let's take a look at the following example: [SQL] db2 => select * from test01 COL1 COL2 ----- A01--B01--A02 B02 A03-db2 => select count (*) cnt1, count (1) cnt2, count (col1) cnt3, count (col2) cnt4 from test01 CNT1 CNT2 CNT3 CNT4 ---------------------------- ----- ----------- 5 5 3 2 count: collects statistics on records that meet the condition. That is to say, after reading a record and determining that it meets the condition, we increase the statistical variable by 1, in this case, the next record-compliant data is automatically incremented by 1 until the end of the data in the table is read. We can use sum to replace count, that is to say, add 1 when the matching record is found, and add 0 if the matching record is not found. In this way, sum can be used to replace count to count the data in the employee table, and only one table needs to be read: [SQL] select count (*) all, sum (case when sex = 'M' then 1 else 0 end) man, sum (case when sex = 'F' then 1 else 0 end) femail from employee all man femail ----------- 42 23 19 This query is used most in practice. Based on the same input, the number of records with different marks in one or more fields is counted. -- The end --