GROUP BY hupanga safu zinazoshiriki thamani, ili aggregate functions zikokotoe kwa kila kundi. HAVING huchuja makundi hayo (kama WHERE, lakini kwa makundi/aggregates). Kwa pamoja ni kiini cha kufanya muhtasari na kuchambua data kwa kategoria.
country, () user_count, (age) avg_age
users
country;
GROUP BY country huviringisha safu zote za kila nchi kuwa kundi moja, na aggregate functions (COUNT, AVG) hukokotoa thamani kwa kila kundi. Unaweza kupanga kwa safuwima nyingi:
GROUP BY country, city -- a group per unique (country, city) combination
-- 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 huchuja baada ya kupanga, kwenye thamani za aggregate — jambo ambalo WHERE haiwezi kufanya (WHERE huchuja safu binafsi kabla ya kupanga).
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)
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.)
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
(WHERE before grouping, HAVING after — which is why their roles differ)
GROUP BY na HAVING ni kiini cha SQL ya uchambuzi — kufanya muhtasari na kuchambua data kwa kategoria ni hitaji la msingi, la mara kwa mara (hesabu kwa kila mkoa, mauzo kwa kila bidhaa, wastani kwa kila kundi), hivyo kuzielewa ni muhimu. GROUP BY (kupanga safu ili aggregates zikokotoe kwa kila kundi) ni msingi wa maswali haya ya muhtasari, na kuelewa jinsi inavyoviringisha safu kuwa makundi kwa ajili ya aggregation ni muhimu kwa ripoti au uchambuzi wowote.
Tofauti ya WHERE dhidi ya HAVING ni muhimu hasa na huchanganywa mara kwa mara: WHERE huchuja safu binafsi kabla ya kupanga (na haiwezi kutumia aggregates), wakati HAVING huchuja makundi baada ya kupanga (kwenye thamani za aggregate) — kutumia isiyofaa husababisha hitilafu au matokeo yasiyo sahihi, na kuelewa lini kila moja inatumika (WHERE kwa uchujaji wa kiwango cha safu, HAVING kwa uchujaji wa kiwango cha aggregate wa makundi) ni ujuzi muhimu.
Kujua kuchuja kwa WHERE kwanza inapowezekana (yenye ufanisi zaidi — kupunguza safu kabla ya kupanga) ni uboreshaji wenye manufaa.
Uhusiano na mpangilio wa utekelezaji (WHERE kabla ya kupanga, HAVING baada yake) huelezea kwa nini majukumu yao yanatofautiana.
Kwa kuwa kupanga na uchujaji wa aggregate vimeenea katika ripoti na uchambuzi wa data, na kwa kuwa tofauti ya WHERE-dhidi-ya-HAVING ni muhimu kwa usahihi na pia hueleweka vibaya mara kwa mara, kufahamu vyema GROUP BY na HAVING — kupanga kwa aggregates za kila kategoria na modeli ya uchujaji wa ngazi mbili — ni maarifa ya msingi, yanayotumika mara kwa mara muhimu kwa SQL ya uchambuzi na mada ya kawaida ya mahojiano.