Explanation of using if judgment conditions in sum and count functions when using SQL statements to collect data

First, let me give you an example (if you don’t want to read it, just read the summary below):

order_type: order type open_id: user unique identifier SELECT
    date(create_time) AS 'today's date',
    sum(real_price) AS 'Total revenue for the day',
The if judgment condition is used in the sum function:
sum(
IF (order_type = 0, real_price, 0)
) AS 'Pay income on the same day',
sum(
IF (order_type = 1, real_price, 0)
) AS 'Reward income for the day',
}
    count(DISTINCT open_id) AS 'Total number of paying customers',
The count function uses the if condition:
count(
DISTINCT open_id,
IF (order_type = 0, TRUE, NULL)
) AS 'Number of payees',
count(
DISTINCT open_id,
IF (order_type = 1, TRUE, NULL)
) AS 'Number of Rewards',
}
    count(id) AS 'Total number of paid orders',
The count function uses the if condition:
count(
DISTINCT id,
IF (order_type = 0, TRUE, NULL)
) AS 'Number of payment orders',
count(
DISTINCT id,
IF (order_type = 1, TRUE, NULL)
) AS 'Number of reward orders'
}
FROM
orders
WHERE
'real_price' != 1
AND 'status' != 0
GROUP BY DATE(create_time)

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

summary:

The format of if judgment condition in sum function is: sum(if(condition, column value, 0))

Note: sum is a sum function. When the condition is true, the column value (field name) is summed up, that is, accumulated. When the condition is false, the sum is 0 (of course, it is still 0)

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

2. Multiple condition judgment format, sum(if(condition field name>value AND condition field name>value AND condition field name=value,1,0))

Note: The overall meaning of the multi-condition judgment format is to calculate the total number of data that meets the conditions. If the conditions are met, the total number of data is increased by 1, so 1 means accumulation of 1.

3. Common case when format, sum(case when condition field name in (smaller range value, larger range value) then [field name to calculate sum] else 0 end)

The if conditional format used in the count function is:

1. Count the total number, count(if(condition field name = value, true, null))

2. Count the total number of duplicate values, count(DISTINCT field name to be counted, if(condition field name = value, true, null))

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links