SQL функции "GROUP BY" и "HAVING".

fgh's picture

 
CREATE TABLE CUSTOMERS(
  ID   INT              NOT NULL,
  NAME VARCHAR (20)     NOT NULL,
  AGE  INT              NOT NULL,
  ADDRESS  CHAR (25) ,
  SALARY   DECIMAL (18,3),        
  PRIMARY KEY (ID)
);
 
insert into CUSTOMERS (ID, NAME, AGE, ADDRESS,SALARY) values(
1, "Mike",25,"Green street",20000);
insert into CUSTOMERS (ID, NAME, AGE, ADDRESS,SALARY) values(
2, "Jhon",28,"Yellow street",25000);
insert into CUSTOMERS (ID, NAME, AGE, ADDRESS,SALARY) values(
3, "Jaison",21,"Red street",16000);
insert into CUSTOMERS (ID, NAME, AGE, ADDRESS,SALARY) values(
4, "White",23,"Blue street",18000);
insert into CUSTOMERS (ID, NAME, AGE, ADDRESS,SALARY) values(
5, "Heila",23,"Blue street",18000);
insert into CUSTOMERS (ID, NAME, AGE, ADDRESS,SALARY) values(
6, "Jhon",23,"Blue street",18000);
insert into CUSTOMERS (ID, NAME, AGE, ADDRESS,SALARY) values(
7, "Bill",23,"Blue street",18000);
insert into CUSTOMERS (ID, NAME, AGE, ADDRESS,SALARY) values(
8, "Donald",23,"Blue street",18000);
 
SELECT AGE, SUM(SALARY) as SUM FROM  CUSTOMERS GROUP BY AGE;

HAVING позволяет отфильтровать то, что сгруппировано с помощью GROUP BY.
Например допишем:

SELECT age, SUM(salary) as sum FROM CUSTOMERS GROUP BY age 
HAVING SUM>16000 AND SUM<25000;

Запустить код: https://paiza.io/en/languages/mysql

Источники:
1) http://old.code.mu/sql/group-by.html
2) http://old.code.mu/sql/having.html