聚合函数 ​

SELECT
	MAX(invoice_total) as highest,
    MIN(invoice_total) as lowest,
    AVG(invoice_total) as average,
    SUM(invoice_total) as total,
    -- 所有的变量都可以执行运算
    SUM(invoice_total * 10) as total2
FROM sql_invoicing.invoices;

SELECT
    -- 查询表中所有的 row
    COUNT(*) as counts,
    -- 查询某个字段，null 会被过滤
    COUNT(payment_date) as payment_date_count,
    -- 去重复查询
    COUNT(DISTINCT client_id) as counts
FROM sql_invoicing.invoices;

SELECT
	client_id,
	SUM(invoice_total) as total
FROM invoices
WHERE invoice_date > '2019-07-01'
GROUP BY client_id
ORDER BY total DESC

-- 多表 group by
SELECT
	payments.date,
    payment_methods.name,
    SUM(payments.amount) as total_pay
FROM payments
JOIN payment_methods
ON payment_methods.payment_method_id = payments.payment_method
GROUP BY payments.date,payment_methods.name
ORDER BY payments.date

SELECT
	client_id,
	SUM(invoice_total) as total
FROM invoices
--
-- WHERE total > 400
GROUP BY client_id
HAVING total > 400

SELECT state, city, sum(invoice_total)
FROM invoices i
JOIN clients c USING(client_id)
GROUP BY state, city WITH ROLLUP;