Hawlaha Koobida iyo GROUP BY

Warbixinta dhabta ah badanaa ma ahan “hel safafka” — waxay badanaa tahay “iisu celi”, “soo samaynta wadarta,” ama “hel celceeliska.” Halkaas ayuu yimaadaa GROUP BY iyo hawlaha koobida (aggregate functions).

Hawlaha Koobida Aasaasiga

-- Tirada safaf
SELECT COUNT(*) FROM dalabka;
SELECT COUNT(email) FROM macaamiilka; -- tiro kaliya meesha email jirto

-- Wadarta
SELECT SUM(xad * qiimaha) AS dakhliga_guud FROM dalabka;

-- Celceeliska
SELECT AVG(mushaharka) AS mushahar_celceeliska FROM shaqaalaha;

-- Ugu yar / ugu badan
SELECT MIN(qiimaha) AS ugu_jaban FROM alaabta;
SELECT MAX(qiimaha) AS ugu_qaali FROM alaabta;

GROUP BY — Kala Samaynta Kooxo

GROUP BY wuxuu koobaa kooxo natiijooyinka iyadoo ku saleysan qiimayaasha hal tiir (ama in ka badan):

-- Tirada dalabka qof kasta
SELECT
  isticmaale_id,
  COUNT(*)    AS tirada_dalabka,
  SUM(wadarta) AS guud_wadarta
FROM dalabka
GROUP BY isticmaale_id;

-- Lacag-helka sannadle ah ee waax kasta
SELECT
  waaxda,
  COUNT(*)      AS tirada_shaqaalaha,
  AVG(mushaharka) AS mushahar_celceeliska,
  SUM(mushaharka) AS miisaaniyadda_guud
FROM shaqaalaha
GROUP BY waaxda
ORDER BY miisaaniyadda_guud DESC;

HAVING — Shaandheynta Kooxaha

WHERE wuxuu shaandheeyaa safaf kasta kahor koobida. HAVING wuxuu shaandheeyaa ka dib koobida — wuxuuna shaqeeyaa kooxaha:

-- Isticmaalayaasha 5-ka dalabba ka badan
SELECT
  isticmaale_id,
  COUNT(*) AS tirada_dalabka
FROM dalabka
GROUP BY isticmaale_id
HAVING COUNT(*) > 5
ORDER BY tirada_dalabka DESC;

-- Waaxaha mushahar celceeliska $60,000 ka sarreeya
SELECT
  waaxda,
  AVG(mushaharka) AS mushahar_celceeliska
FROM shaqaalaha
GROUP BY waaxda
HAVING AVG(mushaharka) > 60000;

Xeerka fudud: WHERE → safaf. HAVING → kooxo.

Tusaale Dhamaystiran: Warbixinta Iibka

SELECT
  nooca_alaabta,
  COUNT(*)              AS tirada_dalabka,
  SUM(d.xad)           AS guud_xad,
  ROUND(AVG(a.qiimaha), 2) AS qiimaha_celceeliska,
  SUM(d.xad * a.qiimaha) AS dakhliga_guud
FROM dalabka        AS d
JOIN alaabta        AS a ON d.alaab_id = a.id
WHERE d.taariikh >= '2024-01-01'
GROUP BY nooca_alaabta
HAVING SUM(d.xad * a.qiimaha) > 10000
ORDER BY dakhliga_guud DESC;

Waxay ku soo celisaa: Noocyada alaabta dakhligoodii $10,000 ka badan uu yahay sannadkan — oo kala soocaya ugu dakhliya badan.

Xukumista Tartanka Qorista: SQLSAM

Xeer fudud oo lagu xusuusto tartanka saxa ah:

S — SELECT
F — FROM
W — WHERE
G — GROUP BY
H — HAVING
O — ORDER BY
L — LIMIT

Database-ka si kala duwan ayuu u fuliyaa, laakiin qorista waa in raacto nidaamkan.

Hawlaha Qoraalka oo ku Xidha

-- CONCAT: isku dar qoraalka
SELECT CONCAT(magaca_hore, ' ', magaca_dambe) AS magaca_buuxa
FROM shaqaalaha;

-- LENGTH: dhererka xadhiga
SELECT magaca, LENGTH(magaca) AS dhererka
FROM alaabta
ORDER BY dhererka DESC;

-- UPPER / LOWER
SELECT UPPER(email) FROM macaamiilka;

-- SUBSTRING
SELECT SUBSTRING(taleefanka, 1, 3) AS code_dalka FROM macaamiilka;