#aggregate function

[trytolearnpython]

Database - Aggregate Function

AVG

SELECT AVG(amount) FROM payment;

就會顯示出總額的平均值

SELECT ROUND(AVG(amount), 2) FROM payment;

就會取道小數點後第二位

MIN

SELECT MIN(amount) FROM payment;

取最小值

MAX

SELECT MAX(amount) FROM payment;

取最大值

SUM

SELECT SUM(amount) FROM payment;

總額

GROUP BY

SELECT column_1, aggregate_function(column_2)

FROM table_name

GROUP BY column_1;

如果今天我們要看客人總共花了多少錢，先用GROUP BY他就會先把customer_id作為分組，例如ID是3的客人，他的花費都會被歸在他這一組

所以如果只用GROUP BY而沒有用aggregate_function就會很像用DISTINCT

SELECT customer_id, SUM(amount)

FROM payment

GROUP BY customer_id;

這樣就可以知道每個客人總額花了多少

加上ORDER BY SUM(amount)

就可以排序了

SELECT staff_id, COUNT(amount)

FROM payment

GROUP BY customer_id;

這樣我就可以看，哪個員工經手最多比數交易

5/27

HAVING

SELECT column_1, aggregate_function(column_2)

FROM table_name

GROUP BY column_1

HAVING condition;

其實就很像是where，只是是用在GROUP BY

如果用WHERE他會先在GROUP BY之前先作用，而HAVING則是在GROUP BY之後才作用