Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of using if judgment condition in sum and count function in SQL statement statistical data. The article is very detailed and has a certain reference value. Interested friends must read it!

Example:

Order_type: order type open_id: user uniquely identifies SELECT date (create_time) AS 'date of the day', sum (real_price) AS 'total income of the day', if is used in the sum function to judge: {sum (IF (order_type = 0, real_price, 0)) AS 'revenue of the day paid', sum (IF (order_type = 1, real_price, 0)) AS 'revenue of the day of reward' } count (DISTINCT open_id) AS 'total number of payers', count function uses if to judge condition: {count (DISTINCT open_id,IF (order_type = 0, TRUE, NULL)) AS 'number of payments', count (DISTINCT open_id,IF (order_type = 1, TRUE, NULL) AS 'number of people',} count (id) AS 'total paid orders', count function uses if to judge condition: {count (DISTINCT id,IF (order_type = 0)) TRUE, NULL) AS 'paid orders', count (DISTINCT id,IF (order_type = 1, TRUE, NULL)) AS 'reward orders'} FROMordersWHERE'real_price'! = 1AND 'status'! = 0GROUP BY DATE (create_time)

Query results: in order to distinguish the data statistics of reward orders and payment orders, make the data clearer.

Summary:

If is used in sum function to determine the condition format: sum (if (condition, column value, 0))

Note: sum is a summation function. If the condition is true, the column value (field name) summation is cumulative. If the condition is false, it is 0 summation (or 0, of course).

1. Single conditional judgment format, sum (if (conditional field name = value, field name of sum needs to be calculated, 0))

two。 Multiple conditional judgment format, sum (if (conditional field name > value AND conditional field name > value AND conditional field name = value, 1J0))

Note: the overall meaning of the multi-conditional judgment format is to calculate the total number of data that meet the conditions, and if the conditions are met, the total number of data is added to 1, so 1 means to accumulate 1.

3. Common case when format, sum (case when conditional field name in (small range value, large range value) then [field name of sum to be calculated] else 0 end)

The judgment condition format using if in the count function is as follows:

1. Statistics total, count (if (conditional field name = value, true,null))

two。 Count the total to remove duplicate values, count (DISTINCT needs to calculate the field name of count, if (conditional field name = value, true,null))

The above is all the contents of the article "sample Analysis of the use of if judgment conditions in sum and count functions in SQL statement Statistics". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.