Abubuwan haɗa-haɗa (COUNT, SUM, AVG, da sauransu) nini ne?

Question

IT Interview Practice · Accepted Answer

**Abubuwan haɗa-haɗa** suna ƙididdige ƴar ta'adda daya daga jerin layin — `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`. Suna da mahimmanci don taƙaitawa ta bayani (jimla, matsakaitawa, ƙididdige), musamman lokacin da aka haɗa tare da `GROUP BY`.

## Abubuwan haɗa-haɗa na yau da kullun

```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;
```

Kowane aiki yana rage layin da yawa zuwa ƴa ta'adda ɗaya. `SUM`/`AVG` suna aiki akan lambobi; `MIN`/`MAX` akan lambobi, kwanaki, ko rubutu; `COUNT` akan kome.

## COUNT(*) da COUNT(column) — bambancin da ya haɗi dalilin

```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(*)`** yana ƙididdige layin duka, amma **`COUNT(column)`** yana ƙididdige layin da kawai inda waccan ginshiki ba shi da **NULL** — canji da ke da azanci amma mahimmanci. `COUNT(DISTINCT col)` yana ƙididdige ƴaƴya daban.

## Abubuwan haɗa-haɗa tare da GROUP BY (taƙaitawa ta kwan-kwan)

```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
```

Ba tare da `GROUP BY`, abubuwan haɗa-haɗa sun taƙaita sakamakon sabiwa duka cikin layi ɗaya. Tare da `GROUP BY`, yana samarwa layi ɗaya na taƙaita **per group** — aiki na yau da kullun, mai iko.

## Kuskuri mai sarari: abubuwan haɗa-haɗa da gin da ba su haɗa ba

```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;
```

## Abin da ya haɗi dalilin

Abubuwan haɗa-haɗa suna da mahimmanci ga **taƙaitawa da nazarfadiye bayani** — ƙididdige jimla, matsakaitawa, ƙididdige, da gitare shine buƙatu na jiya-jiya a cikin rajistar bayani, nazarfadiye, dashboard, da dabbar ilimin aiki, saboda haka fahimta su ita ce asasiyar ilimin SQL.

Sanin abubuwan da suka iri-iri (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`) da yadda suke rage seti na layin zuwa ta'addun ta'adda daya ya dace da kowa.

Bambanci mai mahimmanci da ba a fahimta sosai ba shi ne bambancin **`COUNT(*)` da `COUNT(column)`** (`COUNT(*)` yana ƙididdige layin duka, `COUNT(column)` yana ƙididdige layin da NULL ba ne kawai — wanda zai iya samarwa sakamakon da ya bambanta, da ke mamaki), tare da `COUNT(DISTINCT)` don ƴaƴya daban.

Mafi karfi, abubuwan haɗa-haɗa suna haɗa tare da **`GROUP BY`** don samarwa taƙaitawa ta kwan-kwan (ƙididdige/matsakaitawa per nau'i) — tushe na bubugan nazarfadiye.

Fahimta **kuskuri** cewa ba za a iya haɗa abubuwan haɗa-haɗa tare da gin da ba a ƙungura ba (kuskuri na yau da kullun) ita ce mahimmanci har yanzu.

Sabibi da taƙaitawa ta bayani shine aiki na jiya-jiya a gidaje bayani (kowane rajista, dashboard, da bubugan nazarfadiye suna amfani da abubuwan haɗa-haɗa), kuma fahimta abubuwan, takaici na COUNT, da musamman haɗin su tare da GROUP BY ita ce asasiyar nazarfadiye bayani a cikin SQL, yin nasara abubuwan haɗa-haɗa ita ce asasiyar ilimin aiki-aiki da kafa daga bubugan bubugan nazarfadiye da ikon nazarfadiye wanda ke sa SQL na mahimmanci ga rajistar bayani da anzaloji.

Abubuwan haɗa-haɗa na yau da kullun

COUNT(*) da COUNT(column) — bambancin da ya haɗi dalilin

Abubuwan haɗa-haɗa tare da GROUP BY (taƙaitawa ta kwan-kwan)

Kuskuri mai sarari: abubuwan haɗa-haɗa da gin da ba su haɗa ba

Abin da ya haɗi dalilin

Tambayoyi masu alaƙa