• 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;
    
  • 相关阅读:
    HTML当中特殊字符的表示
    溢出文本用“...”代替
    【转】图标字体化浅谈
    字体在网页中画ICON图标
    图片轮播
    js获取网页屏幕可视区域高度
    MVC入口程序 | 简单调用及实例化
    初学者对于MVC架构模式学习与理解
    PHP初学习笔记(2015/4/8)
    linux常用20命令 --转载
  • 原文地址:https://www.cnblogs.com/jly1/p/12977428.html
Copyright © 2020-2023  润新知