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

Implementation example of video player based on Vue

Recommend

Detailed tutorial for installing ffmpeg under Linux

1. Install ffmpeg under centos linux 1. Download ...

Implement dynamic management and monitoring of docker containers based on spring-boot and docker-java [with complete source code download]

Introduction to Docker Docker is an open source a...

How to add conditional expressions to aggregate functions in MySql

Implementation example of video player based on Vue

Introduction to CSS style classification (basic knowledge)

Detailed explanation of the execution process of JavaScript engine V8

MySQL 5.7.25 compressed version installation and configuration method graphic tutorial

Two ways to write stored procedures in Mysql with and without return values

Detailed explanation of the application of CSS Sprite

Detailed explanation of how to prevent content from being selected, copied, or right-clicked in HTML pages

Solve the problem that changes to the Docker MySQL container database do not take effect

Explanation of the concept and usage of Like in MySQL

Using MySQL in Windows: Implementing Automatic Scheduled Backups

Recommend

Detailed tutorial for installing ffmpeg under Linux

Implement dynamic management and monitoring of docker containers based on spring-boot and docker-java [with complete source code download]

mysql5.7.19 zip detailed installation process and configuration

Four ways to switch tab pages in VUE

jQuery implements article collapse and expansion functions

Angular environment construction and simple experience summary

HTML code to add icons to transparent input box

Vue3 implements CSS infinite seamless scrolling effect

Detailed explanation of how to use join to optimize SQL in MySQL

The problem of Chinese garbled characters appearing when connecting to MySQL database in Idea

Introduction to the use of the indeterminate property of the checkbox

Linux CentOS 6.5 Uninstall, tar and install MySQL tutorial

How to transfer files between Docker container and local machine

Complete steps to configure basic user authentication at the Nginx level

IE6 distortion problem