• GROUP BY子句


    GROUP BY 常用的聚合函数, 可以对数据进行汇总

    • 一般与常见的一些函数一起使用, 汇总数据后一般没有办法进行使用
    • 使用顺序, SELECT, FROM, JOIN, WHERE GROUP BY, HAVING, ORDER BY, LIMIT
    -- 使用聚合函数
    -- MAX()
    -- MIN()
    -- AVG()
    -- SUM()
    -- COUNT();
    
    SELECT 
    	MAX(invoice_total) AS highest,
        MIN(invoice_total) AS lowest,
        AVG(invoice_total) AS average,
        SUM(invoice_total) AS total,
        SUM(invoice_total * 1.1) AS number_of_invoices,
        COUNT(payment_date) AS count_of_payments,
        COUNT(*) AS total_records
    FROM invoices
    WHERE invoice_date > '2019-07-01';
    
    USE sql_invoicing;
    SELECT 
    	MAX(invoice_total) AS highest,
        MIN(invoice_total) AS lowest,
        AVG(invoice_total) AS average,
        SUM(invoice_total) AS total,
        COUNT(invoice_total * 1.1) AS number_of_invoices,
        COUNT(payment_date) AS count_of_payments,
        COUNT(DISTINCT client_id) AS total_records
    FROM invoices
    WHERE invoice_date > '2019-07-01';
    
    SELECT 
    	SUM(invoice_total) AS total_sales,
        SUM(payment_total) AS total_payments,
        SUM(invoice_total) - SUM(payment_total) AS what_we_expect
    FROM invoices;
    -- Exercise
    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-06-30'
    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-30'
    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-30';
        
    -- 数据分组
    SELECT 
    	state,
            city,
    	client_id,
    	SUM(invoice_total) AS total_sales
    FROM invoices i
    JOIN clients USING (client_id)
    WHERE invoice_date >= '2019-07-01'
    GROUP BY client_id
    ORDER BY total_sales DESC;
    
    SELECT 
    	state,
        city,
    	SUM(invoice_total) AS total_sales
    FROM invoices i
    JOIN clients USING (client_id)
    WHERE invoice_date
    GROUP BY state, city;
    
    SELECT 
    	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, payment_method
    ORDER BY date;
    
    -- HAVING
    SELECT 
    	client_id,
        SUM(invoice_total) AS total_sales,
        COUNT(*) AS number_of_invoices
    FROM invoices
    GROUP BY client_id
    HAVING total_sales > 500;
    
    SELECT 
    	client_id,
        SUM(invoice_total) AS total_sales,
        COUNT(*) AS number_of_invoices
    FROM invoices
    GROUP BY client_id
    HAVING total_sales > 500 AND number_of_invoices > 5;
    
    -- Exercise
    -- Get the customers
    -- located in Virginia
    -- Who have spent more than 100
    USE sql_store;
    
    
    SELECT 
    	order_id,
    	first_name, 
        last_name,
        state,
        SUM(quantity * unit_price) AS total_price
    FROM customers c
    JOIN orders o 
    	USING (customer_id)
    LEFT JOIN order_items oi
    	USING (order_id)
    GROUP BY order_id
    HAVING state = 'VA' and total_price > 60;
    
    
    SELECT 
    	c.customer_id,
        c.first_name,
        c.last_name,
        SUM(oi.quantity * oi.unit_price) AS total_sales
    FROM customers c
    JOIN orders o 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_sales > 100;
    
    -- WITH ROLLUP
    USE sql_invoicing;
    SELECT 
    	client_id,
        SUM(invoice_total) AS total_sales
    FROM invoices
    GROUP BY client_id WITH ROLLUP;
    
    -- 分组,只能在MySQL用
    SELECT 
    	state,
        city,
        SUM(invoice_total) AS total_sales
    FROM invoices i
    JOIN clients c USING (client_id)
    GROUP BY state, city WITH ROLLUP;
    
    -- Exercise
    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;
    
    SELECT 
    	pm.name AS payment_method,
        SUM(amount) AS total
    FROM payments p
    JOIN payment_methods pm
    	ON p.payment_method = pm.payment_method_id
    GROUP BY pm.name WITH ROLLUP;
    
  • 相关阅读:
    1123 Is It a Complete AVL Tree (30分)---如何建立平衡二叉搜索树(LL型RR型LR型RL型)+如何判断完全二叉树
    1021 Deepest Root (25 分)(经典搜索)
    PAT甲 1020 Tree Traversals (树的后序中序->层序)
    (数据结构)如何根据树的后序中序遍历求树的前序遍历
    习题2.3 数列求和-加强版 (模拟)
    PAT甲级 1051 Pop Sequence (25) && 2019天梯赛 L2-032 彩虹瓶 (25 分) (模拟+栈)
    PAT甲级 Are They Equal (25) (恶心模拟)
    PAT甲级1059 Prime Factors (25)(素数筛+求一个数的质因子)
    IO 模型
    Nginx 反向代理
  • 原文地址:https://www.cnblogs.com/jly1/p/12977428.html
Copyright © 2020-2023  润新知