1.聚合函数

SQL中有一些自带的函数，其中一些叫做聚合函数

常见函数：MAX(),MIN(),AVG(),SUM()，COUNT()--这些函数我们在学算法的时候经常看到，看到名字就知道啥意思

SELECT MAX(invoice_total)
FROM invoices

比如用max（）函数筛选总价最高的，执行后你会发现列名变成了

这些函数只运算非空数据

当用COUNT()函数获取数量的时候，如果想去除重复的项可以在里面加上DISTINCT

COUNT(DISTINCT client_id)--这样就去除了相同client——id的数据，只记录一次

SELECT 'First half 0f 2019' AS date_range,
        SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payment,
        SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date  BETWEEN'2019-01-01'AND'2019-06-30'
UNION
SELECT 'Second half 0f 2019' AS date_range,
        SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payment,
        SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date  BETWEEN'2019-07-01'AND'2020-01-01'
UNION
SELECT 'Total 0f 2019' AS date_range,
        SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payment,
        SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date >'2019-01-01'
​

练习

GROUP BY子句

上面我们用一个简单的SUM（）就算出了某时间段的销售额总数，可是如果你想知道每个客户的总销售额的话就要用到子句GROUP BY

GROUP BY 列名会根据后面的列名来进行分组比如

GROUP BY client_id

就是client_id一样的为一个组将其信息整合

默认是我们定义的那个列进行排序的，也可以用ORDER BY来调整顺序

SELECT 
        client_id,
        SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC

注意：如果要加条件语句WHERE请放在GROUP BY 前面

SELECT 
        p.date,
        pm.name AS payment_method,
        SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
GROUP BY date,name

例子

HAVING子句

用于分组过后对数据进行筛选，因为where不能在GROUP BY 后面

SELECT 
        client_id,
        SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales>500

其也是一个条件语

注意：HAVING里面的列名必须在SELECT里面，SELECT下面没有但是表里面有的列名是不能用的

练习：

SELECT 
        c.customer_id,
        c.first_name,
        c.last_name,
        c.state,
        sum(oi.quantity*oi.unit_price) AS total_spent
FROM orders o
JOIN customers c
USING (customer_id)
JOIN order_items oi
USING (order_id)
WHERE state='VA'
GROUP BY c.customer_id,
        c.first_name,
        c.last_name
HAVING total_spent>100

ROLLUP运算符

sql的WITH ROLLUP用于汇总数据

我们在GROUP BY 后面加上这个运算符系统就会计算所显示的数据的总和在后面

当然只会计算数值

练习：

SELECT 
        pm.name AS payment_method,
        SUM(p.amount) AS total
FROM payments p
JOIN payment_methods pm
ON p.payment_method=pm.payment_method_id
GROUP BY pm.name WITH ROLLUP