[Statistical techniques for mysql]group by and having a binding function

Group BY and having allow fields to use functions, depending on the result of the function run the group by group or having set selection criteria;

The group by and having also allow the use of field aliases

Example Table A:

Id	A	Effective_date
38	1	2018-03-23 09:55:58
39	9	2018-03-22 09:56:04
66	1	2018-02-21 09:56:09
68	6	2018-01-13 09:56:17
69	1	0000-00-00 00:00:00

Example Table B:

Id	u_id	Amount	T4_date
1	39	30	2017-07-28 15:30:00
2	66	21st	2018-03-08 10:07:40
3	69	13	2018-03-01 10:09:00

Examples of applications:

1. Single-table formatting grouping

SELECT sum  from GROUP  by Date_format (effective_date, "%Y-%m");

SUM (a)

1

6

1

10

2. Single-table formatting groupings (using aliases)

SELECT Date_format (effective_date, "%Y-%assumfromGROUPby date;

Date	SUM (a)
0000-00	1
2018-01	6
2018-02	1
2018-03	10

3. Single-table format grouping, having the option to output rows (using aliases)

SELECT Date_format (effective_date, "%Y-%assumfromGROUPby  Having= "2018--";

Date	SUM (a)
2018-02	1

4. List Application

 select  date_format (b.t4_date, "%  y< Span style= "COLOR: #808080" >-%  m ") as  date,  Sum  (A.A) from  test_1 as  a left  join  test_4 as  b on  a.id =  b.u_id group  by  date having  date =  "2018  - 03 "; 

Date	SUM (A.A)
2018-03	2

[Statistical techniques for mysql]group by and having a binding function