Day7 聚合計算

今天用同一張表繼續介紹 聚合，having，groupby

今天用到的 table

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    salesperson VARCHAR(255),
    region VARCHAR(255),
    amount NUMERIC
);

INSERT INTO sales (salesperson, region, amount) VALUES
('Alice', 'North', 500),
('Bob', 'North', 600),
('Alice', 'South', 700),
('Charlie', 'East', 400),
('Alice', 'North', 200),
('Bob', 'South', 300);

聚合函式簡單介紹，更多可以參考

sum: 計算欄位內容的總和

count: 計算有幾筆資料(record, row)

min, max：找出資料row 裡面的最大值或最小值(數值、日期、甚至文字也都可以)

Getting Started

---查詢銷售記錄的數量
SELECT COUNT(*) AS total_records FROM sales;

---計算銷售額不為 NULL 的記錄
SELECT COUNT(amount) AS total_sales_records FROM sales;

SELECT MIN(amount) AS min_sales FROM sales;

SELECT 
    MIN(amount) AS min_sales, 
    MAX(amount) AS max_sales
FROM sales;

明天繼續介紹 groupby 跟 having 關鍵字，這兩個跟聚合函數有關！