Apa iku GROUP BY lan HAVING?

Question

IT Interview Practice · Accepted Answer

**`GROUP BY`** nglumpukaké baris-baris sing duwé nilai padha, supaya fungsi agregat nghitung per grup. **`HAVING`** ngsaring grup-grup kuwi (kaya `WHERE`, nanging kanggo grup/agregat). Bebarengan-bebarengan iku inti saka nyimpulaké lan nganalisá data miturut kategori.

## GROUP BY — agregat per grup

```sql
SELECT country, COUNT(*) AS user_count, AVG(age) AS avg_age
FROM users
GROUP BY country;
-- one result row PER country, with that country's count and average age
```

`GROUP BY country` nglumpukaké kabeh baris saben negara dadi siji grup, lan fungsi agregat (`COUNT`, `AVG`) nghitung nilai saben grup. Bisa nglumpuk miturut pirang-pirang kolom:

```sql
GROUP BY country, city          -- a group per unique (country, city) combination
```

## HAVING — ngsaring grup-grup

```sql
-- show only countries with MORE THAN 100 users
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;          -- filter on the AGGREGATE
```

`HAVING` ngsaring *sawise* nglumpuk, ing nilai agregat — apa sing `WHERE` ora bisa nindakaké (WHERE ngsaring baris-baris individu sadurunge nglumpuk).

## WHERE vs HAVING — bédaning penting

```sql
SELECT country, COUNT(*) AS cnt
FROM users
WHERE active = true             -- WHERE: filter ROWS first (before grouping)
GROUP BY country
HAVING COUNT(*) > 100;          -- HAVING: filter GROUPS after (on aggregates)
```

```text
WHERE  → filters individual ROWS, runs BEFORE grouping, can't use aggregates
HAVING → filters GROUPS, runs AFTER grouping, used WITH aggregates
→ Use WHERE to filter rows, HAVING to filter aggregated groups. (Filter with WHERE
  first when possible — it's more efficient, reducing rows before grouping.)
```

## Urutan eksekusi

```text
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
(WHERE before grouping, HAVING after — which is why their roles differ)
```

## Apa perintahe

`GROUP BY` lan `HAVING` iku inti saka **analytical SQL** — nyimpulaké lan nganalisá data miturut kategori iku kabutuhan fundamental sing terus-terusan (cacahing per wilayah, penjualan per produk, rata-rata per grup), dadi ngerti babagan kuwi iku penting banget. `GROUP BY` (nglumpuk baris supaya agregat nghitung per grup) iku fondasi saka query-query ringkesan kuwi, lan ngerti kepiye dheweke nglumpukaké baris dadi grup kanggo agregasi iku perlu kanggo apa-apa laporan utawa analitik.

Bédaning **`WHERE` vs `HAVING`** khusus banget penting lan akeh sing kelepatan: `WHERE` ngsaring baris-baris individu *sadurunge* nglumpuk (lan ora bisa nggunakaké agregat), nalika `HAVING` ngsaring grup-grup *sawise* nglumpuk (ing nilai agregat) — nggunakaké sing salah ngasilake salah utawa asil sing salah, lan ngerti kapan saben-saben pungkasan (WHERE kanggo panyaringan tingkat baris, HAVING kanggo panyaringan tingkat agregat saka grup-grup) iku katrampilan penting.

Ngerti kanggo ngsaring karo WHERE dhisik yen bisa (luwih cepet — nyuda baris sadurunge nglumpuk) iku optimisasi sing berguna.

Koneksine karo **urutan eksekusi** (WHERE sadurunge nglumpuk, HAVING sawise) nerangaké dene kahanane bédha-bédha.

Amarga nglumpuk lan panyaringan agregat iku ing sapiiro-pirone laporan lan analisis data, lan amarga bédaning WHERE-vs-HAVING iku pentinge kanggo kabenaran lan akeh sing kelepatan nggene, nguasaake `GROUP BY` lan `HAVING` — nglumpuk kanggo agregat per-kategori lan model panyaringan dua-tingkat — iku kasuwan inti, kasuwan sing digawakaké asring, penting kanggo analytical SQL lan topik wawancara sing asring dikelakaké.

GROUP BY — agregat per grup

HAVING — ngsaring grup-grup

WHERE vs HAVING — bédaning penting

Urutan eksekusi

Apa perintahe

Pitakon sing gegandhengan