Apa iku fungsi agregat (COUNT, SUM, AVG, lan sapanungan)?

Question

Accepted Answer

**Fungsi agregat** ngitung siji nilai saka set baris — `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`. Iku penting banget kanggo merangkum data (total, rata-rata, jumlah), utamane digabung karo `GROUP BY`.

## Fungsi agregat sing utama

```sql
SELECT
  COUNT(*)        AS total_rows,        -- count all rows
  COUNT(email)    AS rows_with_email,    -- count NON-NULL emails (ignores NULLs!)
  SUM(amount)     AS total_amount,       -- sum of a column
  AVG(price)      AS average_price,      -- average
  MIN(price)      AS lowest,             -- minimum
  MAX(price)      AS highest             -- maximum
FROM orders;
```

Saben fungsi nyuda akeh baris dadi siji nilai. `SUM`/`AVG` kerja ing angka; `MIN`/`MAX` ing angka, tanggal, utawa teks; `COUNT` ing apa wae.

## COUNT(*) lawan COUNT(column) — perbedaan sing penting

```sql
COUNT(*)         -- counts ALL rows (including those with NULLs)
COUNT(email)     -- counts only rows where email is NOT NULL
COUNT(DISTINCT country)  -- counts UNIQUE non-null values
```

**`COUNT(*)`** ngetung kabeh baris, nanging **`COUNT(column)`** ngetung mung baris sing kolom kasebut **ora NULL** — perbedaan sing halus nanging penting. `COUNT(DISTINCT col)` ngetung nilai unik.

## Agregat karo GROUP BY (ringkasan per-grup)

```sql
-- aggregate PER GROUP instead of the whole table
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
GROUP BY country;          -- one result row per country
-- → for each country: how many users and their average age
```

Tanpa `GROUP BY`, agregat merangkum kabeh hasil dadi siji baris. Karo `GROUP BY`, iku ngasilke siji baris ringkasan **per grup** — cara sing tipikal lan kuwat.

## Pitakonan umum: agregat lan kolom sing ora digabung

```sql
-- ❌ ERROR — can't mix an aggregate with a non-grouped column
SELECT name, COUNT(*) FROM users;        -- which name? ambiguous
-- ✅ either group by it, or only select aggregates
SELECT country, COUNT(*) FROM users GROUP BY country;
```

## Mulane iku penting

Fungsi agregat kuwat banget kanggo **merangkum lan menganalisis data** — ngitung total, rata-rata, jumlah, lan ekstrem iku butuh terus ing laporan, analitik, dashboard, lan logika aplikasi, mula mangerti iku katrangan SQL dasar.

Mangerti fungsi utama (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`) lan carane nyuda set baris dadi nilai tunggal iku perlu kanggo ringkasan data apa wae.

Detail penting sing sering disalengo yaiku perbedaan **`COUNT(*)` lawan `COUNT(column)`** (`COUNT(*)` ngetung kabeh baris, `COUNT(column)` ngetung mung nilai non-NULL — sing bisa ngasilake asil beda lan ngejutake), balung karo `COUNT(DISTINCT)` kanggo nilai unik.

Paling kuwat, agregat gabung karo **`GROUP BY`** kanggo ngasilake ringkasan per-grup (jumlah/rata-rata per kategori) — fondasi saka pitakonan analitik.

Mangerti **pitakonan** yen ora bisa campur agregat karo kolom sing ora digabung (kesalahan umum) uga penting.

Amerga merangkum data iku tugas database sing serba-serbi (laporan, dashboard, lan pitakonan analitik apa wae nggunake agregat), lan mangerti fungsi, nuansa COUNT, lan utamane gabungan karo GROUP BY iku dasar kanggo analisis data ing SQL, nguasai fungsi agregat iku katrangan inti sing sering diterapake penting kanggo kerja karo data — mbridging pitakonan dasar lan kekuwatan analitik ringkasan sing nggawe SQL berharga kanggo laporan lan wawasan.