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

Tutorial on how to install and configure the unzipped version of MySql under Windows 10

How to add conditional expressions to aggregate functions in MySql

Tutorial on how to install and configure the unzipped version of MySql under Windows 10

Learn Vue middleware pipeline in one article

Discussion on default margin and padding values of common elements

Vue+Openlayer uses modify to modify the complete code of the element

Summary of new usage of vi (vim) under Linux

Build a file management system step by step with nginx+FastDFS

A troubleshooting experience of centos Docker bridge mode unable to access the host Redis service

Add a floating prompt for the header icon in the ElementUI table

Causes and solutions for MySQL too many connections error

Several ways to use v-bind binding with Class and Style in Vue

Recommend

【Web Design】Share E-WebTemplates exquisite foreign web page templates (FLASH+PSD source file+HTML)

How to create a flame effect using CSS

How to use position:sticky to perfectly solve the problem of small programs adhering to the ceiling

MySql grouping and randomly getting one piece of data from each group

Detailed explanation of calculated properties, monitoring properties and life cycle in Vue.js

CentOS 8 installation diagram (super detailed tutorial)

Reduce memory and CPU usage by optimizing web pages

Detailed explanation of Promises in JavaScript

Ideas and codes for implementing waterfall flow layout in uniapp applet

Introduction to CSS BEM Naming Standard (Recommended)

htm beginner notes (must read for beginners)

Installation and daemon configuration of Redis on Windows and Linux

Instructions for deploying projects to remote machines using the Publish Over SSH plugin in Jenkins

Will the deprecated Docker be replaced by Podman?

A brief discussion on HTML doctype and encoding