MySQL 聚合函数 | Aggregate Functions 汇总数据

 Table：

代码段 

SELECT 
      MAX(invoice_total) AS highest,
      MIN(invoice_total) AS lowest,
      AVG(invoice_total) AS average,

      COUNT(DISTINCT client_id) AS total_records  -- 筛掉重复的
FROM invoices
WHERE invoice_date > '2019-07-01'

 Output:

 ------------------------------------------------------------------:)--------------------------------------------------------------------------------小作业：

做出下面这个表：

 

答案：

SELECT 
      'First half of 2019' AS date_range,
      SUM(invoice_total) AS total_sales,
      SUM(payment_total) AS total_payments,
      SUM(invoice_total - payment_total) AS what_we_expect
      
FROM invoices
WHERE invoice_date between'2019-01-01' AND '2019-07-01'
UNION
SELECT 
      'Second half of 2019' AS date_range,
      SUM(invoice_total) AS total_sales,
      SUM(payment_total) AS total_payments,
      SUM(invoice_total - payment_total) AS what_we_expect
      
FROM invoices
WHERE invoice_date between'2019-07-01' AND '2019-12-31'
UNION
SELECT 
      'Total' AS date_range,
      SUM(invoice_total) AS total_sales,
      SUM(payment_total) AS total_payments,
      SUM(invoice_total - payment_total) AS what_we_expect
      
FROM invoices
WHERE invoice_date between'2019-01-01' AND '2019-12-31'