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