Skip to content

sampleSQL_en.md

Latest commit

 

History

History
87 lines (67 loc) · 2 KB

sampleSQL_en.md

File metadata and controls

87 lines (67 loc) · 2 KB

Simple SQL Example

SUM

查询所有销售的总金额

SELECT SUM(amount_sold) AS total_sales FROM sh.sales;

Query the total amount of all sales

SELECT prod_id, SUM(amount_sold) AS total_sales FROM sh.sales GROUP BY prod_id;

AVG

Query the average amount of all sales

SELECT AVG(amount_sold) AS average_sales FROM sh.sales;

Query the average sales amount of each product

SELECT prod_id, AVG(amount_sold) AS average_sales FROM sh.sales GROUP BY prod_id;

MEDIAN

Query the median amount of all sales

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount_sold) AS median_sales FROM sh.sales;

Query the median sales amount of each product

SELECT prod_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount_sold) AS median_sales FROM sh.sales GROUP BY prod_id;

MAX

Query the maximum amount of all sales

SELECT MAX(amount_sold) AS max_sales FROM sh.sales;

Query the maximum amount of all product

SELECT prod_id, MAX(amount_sold) AS max_sales FROM sh.sales GROUP BY prod_id;

MIN

Query the minimum amount of all sales

SELECT MIN(amount_sold) AS min_sales FROM sh.sales;

Query the minimum sales amount for each product

SELECT prod_id, MIN(amount_sold) AS min_sales FROM sh.sales GROUP BY prod_id;

COUNT

Query the total number of sales records

SELECT COUNT(*) AS total_sales_count FROM sh.sales;

Query the sales record quantity of each product

SELECT prod_id, COUNT(*) AS sales_count FROM sh.sales GROUP BY prod_id;

Comprehensive Example

Query the total sales amount, average sales amount, maximum sales amount, minimum sales amount and number of sales records for each product

SELECT prod_id, 
       SUM(amount_sold) AS total_sales, 
       AVG(amount_sold) AS average_sales, 
       MAX(amount_sold) AS max_sales, 
       MIN(amount_sold) AS min_sales, 
       COUNT(*) AS sales_count 
FROM sh.sales 
GROUP BY prod_id;