How to add conditional expressions to aggregate functions in MySql

MySQL filtering timing of where conditions and having conditions when used with aggregate functions

where filters before aggregation

When a query contains aggregate functions and where conditions, such as
select max(cid) from t where t.id<999
At this time, filtering will be performed first and then aggregation. First filter out the records with ID < 999, then find the largest cid and return it.

Having filters after aggregation

Having is used when grouping to filter the grouping results, and usually contains aggregate functions.

SELECT ip,MAX(id) FROM app
GROUP BY ip
HAVING MAX(id)>=5

First group, then aggregate, and then filter the result set where the aggregate result is greater than or equal to 5

The difference between the two:

where is executed first, and then the aggregate function is executed. Having is executed after the aggregate function is executed.

The following is a supplement

There is a requirement that a table has a status field (1: success, 2: failure, and so on). Now we need to use date grouping to count the number of items in different states.

First write a subquery

select aa.logDate,aa.totalLogs 
 ,(select count(1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT( startTime, '%Y-%m-%d') and executeStatus=1) pendingLogs
 ,(select count(1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT( startTime, '%Y-%m-%d') and executeStatus=2) successLogs
 ,(select count(1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT( startTime, '%Y-%m-%d') and executeStatus=3) errorLogs
 ,(select count(1) from dxp.dxp_handlermodel where aa.logDate=DATE_FORMAT( startTime, '%Y-%m-%d') and executeStatus=4) callbackErrorLogs
from
(
 select
 DATE_FORMAT( a.startTime, '%Y-%m-%d') logDate,
 count(1) totalLogs
 from dxp.dxp_handlermodel a 
 group by DATE_FORMAT( a.startTime, '%Y-%m-%d') 
)

The execution is quite slow, so I wondered if I could add conditions to count, and found the following:

select
DATE_FORMAT( startTime, '%Y-%m-%d') logDate,
 count(1) totalLogs,
 count(if(executeStatus=1,true,null)) pendingLogs,
 count(if(executeStatus=2,true,null)) successLogs,
 count(if(executeStatus=3,true,null)) errorLogs,
 count(if(executeStatus=4,true,null)) callbackErrorLogs
from dxp.dxp_handlermodel
group by DATE_FORMAT( startTime, '%Y-%m-%d')

Easy to understand and highly efficient in execution

Other aggregate functions can also be used, such as SUM and other aggregate functions

Practical example:

select count(if(create_date < '2017-01-01' and host_profile_id = '9294d2bf-f457-4fe5-9a36-e5f832310dc2',true,null)) from profile_visit_log 
-- Equivalent to select count(if(create_date < '2017-01-01',true,null)) count from profile_visit_log where host_profile_id = '9294d2bf-f457-4fe5-9a36-e5f832310dc2'

Well, this is the end of this article. I hope you will support 123WORDPRESS.COM in the future.

You may also be interested in:

MySQL aggregate function sorting
MySQL grouping queries and aggregate functions
Analysis of MySQL query sorting and query aggregation function usage
Usage and performance optimization techniques of aggregate function count in MySQL
MySQL essential basics: grouping function, aggregate function, grouping query detailed explanation

<<: How to handle the loss of parameters when refreshing the page when passing parameters to vue router

>>: Vue Element front-end application development preparation for the development environment

MySQL sliding aggregation/year-to-date aggregation principle and usage example analysis

How to add conditional expressions to aggregate functions in MySql

MySQL sliding aggregation/year-to-date aggregation principle and usage example analysis

Tutorial on installing Ceph distributed storage with yum under Centos7

MySQL green decompression version installation and configuration steps

JS implements sliding up and down on the mobile terminal one screen at a time

Detailed explanation of MySQL user rights management

Detailed explanation of CSS child element fixed positioning solution relative to parent element

How to load Flash in HTML (2 implementation methods)

Vue v-model related knowledge summary

Nest.js hashing and encryption example detailed explanation

MySQL obtains the current date and time function example detailed explanation

Recommend

Solution for using Baidu share on Https page

Will the most successful companies in the future be technology companies or design companies?

Implementation of MySQL master-slave status check

In-depth understanding of JavaScript event execution mechanism

An article to deal with Mysql date and time functions

How to receive binary file stream in Vue to realize PDF preview

How to install binary MySQL on Linux and crack MySQL password

Detailed explanation of setting Context Path in Web application

MySQL select results to perform update example tutorial

Detailed explanation of MySQL covering index

Let's talk briefly about the changes in setup in vue3.0 sfc

MySQL kill command usage guide

Detailed explanation of the new array methods in JavaScript es6

Learn MySQL database in one hour (Zhang Guo)

Web page HTML ordered list ol and unordered list ul