COUNT وSUM وAVG وMIN وMAX

ما الذي تفعله الدوال التجميعية

COUNT

SELECT COUNT(*)         FROM users;          -- counts ALL rows
SELECT COUNT(email)     FROM users;          -- counts rows where email IS NOT NULL
SELECT COUNT(DISTINCT country) FROM users;   -- distinct non-NULL countries

SUM

SELECT SUM(total) FROM orders;
SELECT SUM(total) FROM orders WHERE user_id = 42;

AVG

SELECT AVG(rating) FROM reviews;

MIN وMAX

SELECT MIN(created_at), MAX(created_at) FROM orders;
SELECT MIN(name), MAX(name) FROM users;   -- alphabetic

الدوال التجميعية وNULL

-- Suppose the table is empty or all rating is NULL:
SELECT AVG(rating) FROM reviews;   -- NULL

دمج الدوال التجميعية

SELECT COUNT(*)      AS orders,
       SUM(total)    AS revenue,
       AVG(total)    AS avg_order,
       MIN(total)    AS smallest,
       MAX(total)    AS biggest
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';

استخدام DISTINCT داخل الدوال التجميعية

SELECT COUNT(DISTINCT user_id) AS unique_buyers,
       SUM(DISTINCT total)     AS sum_of_distinct_totals
FROM orders;

الدوال التجميعية من دون GROUP BY

التصفية قبل التجميع

-- Average of only large orders:
SELECT AVG(total) FROM orders WHERE total > 100;

عبارة FILTER (PostgreSQL)

SELECT
  COUNT(*)                                AS total_orders,
  COUNT(*) FILTER (WHERE status = 'paid')  AS paid_orders,
  SUM(total) FILTER (WHERE status = 'paid') AS paid_revenue
FROM orders;

مراجعة

تحقق سريع