Replace count query with sum in DB2

The sum function counts and sums the values of a column. The count function accumulates the columns that meet the conditions. If the conditions are met, add one. The count function is commonly used to count the number of records that meet certain conditions,

The sum function counts and sums the values of a column. The count function accumulates the columns that meet the conditions. If the conditions are met, add one. The count function is commonly used to count the number of records that meet certain conditions,

The sum function counts and sums the column values;

The count function accumulates the columns that meet the conditions. If the conditions are met, add one.

The count function is commonly used to count the number of records meeting certain conditions. For example, to count the number of student boys in the student information table:

Select count (*) from student where sex = 'M'

The sum function is commonly used to sum the data that meets the conditions. For example, calculate the total score of 'Scott 'in the score of the student's score table:

Select sum (achv) from score where stu_name = 'Scott 'and stu_id = '2013'

In fact, in addition to counting the rows in all tables, the count in other cases can be replaced by sum.

For example, the number of boys in the preceding statistics can be written as follows:

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

The sum function is used to replace count male employees:

What if we 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 all to get our data:

For the implementation of this query, we need to read three tables, and the efficiency is naturally low. Why do we collect statistics when reading a table at a time?

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 match the record, which is the same as count;

Count (column_name) is different. It filters out the value of column is null.

Let's take a look at the following example:

Count is to collect statistics on records that meet the conditions. That is to say, after we read a record and determine that it meets the conditions, we will increase the statistical variable by 1,

In this way, 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, 1 is added when a record is found and 0 is added when no matching record is found;

In this way, we can use sum instead of count to count the data in the employee table, and only need to read the table once:

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