Aggregate functions hukokotoa thamani moja kutoka seti ya safu — COUNT, SUM, AVG, MIN, MAX. Ni muhimu kwa kufanya muhtasari wa data (jumla, wastani, hesabu), hasa zikichanganywa na .
Aggregate functions hukokotoa thamani moja kutoka seti ya safu — COUNT, SUM, AVG, MIN, MAX. Ni muhimu kwa kufanya muhtasari wa data (jumla, wastani, hesabu), hasa zikichanganywa na .
GROUP BYSELECT
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;
Kila function hupunguza safu nyingi kuwa thamani moja. SUM/AVG hufanya kazi kwa namba; MIN/MAX kwa namba, tarehe, au maandishi; COUNT kwa chochote.
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(*) huhesabu safu zote, lakini COUNT(column) huhesabu tu safu ambapo safuwima hiyo si NULL — tofauti hila lakini muhimu. COUNT(DISTINCT col) huhesabu thamani za kipekee.
-- 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
Bila GROUP BY, aggregate hufanya muhtasari wa matokeo yote kuwa safu moja. Ikiwa na GROUP BY, hutoa safu moja ya muhtasari kwa kila kundi — matumizi ya kawaida na yenye nguvu.
-- ❌ 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;
Aggregate functions ni muhimu kwa kufanya muhtasari na kuchambua data — kukokotoa jumla, wastani, hesabu, na thamani za mwisho ni hitaji la mara kwa mara katika ripoti, uchambuzi, dashboards, na mantiki ya programu, hivyo kuzielewa ni maarifa ya msingi ya SQL.
Kujua functions kuu (COUNT, SUM, AVG, MIN, MAX) na jinsi zinavyopunguza seti za safu kuwa thamani moja ni muhimu kwa muhtasari wowote wa data.
Kipengele muhimu, kinachoeleweka vibaya mara kwa mara ni tofauti ya COUNT(*) dhidi ya COUNT(column) (COUNT(*) huhesabu safu zote, COUNT(column) huhesabu tu thamani zisizo NULL — ambazo zinaweza kutoa matokeo tofauti, ya kushangaza), pamoja na COUNT(DISTINCT) kwa thamani za kipekee.
Kwa nguvu zaidi, aggregates huchanganyika na GROUP BY kutoa muhtasari kwa kila kundi (hesabu/wastani kwa kila kategoria) — msingi wa maswali ya uchambuzi.
Kuelewa mtego kwamba huwezi kuchanganya aggregates na safuwima zisizo za kundi (hitilafu ya kawaida) pia ni muhimu.
Kwa kuwa kufanya muhtasari wa data ni kazi iliyoenea ya hifadhidata (kila ripoti, dashboard, na swali la uchambuzi hutumia aggregates), na kwa kuwa kuelewa functions, hila za COUNT, na hasa mchanganyiko wao na GROUP BY ni msingi wa uchambuzi wa data katika SQL, kufahamu vyema aggregate functions ni maarifa ya msingi, yanayotumika mara kwa mara muhimu kwa kufanya kazi na data — yakiunganisha uulizaji wa msingi na nguvu ya uchambuzi, ya muhtasari inayofanya SQL kuwa na thamani kwa ripoti na ufahamu.