Naučimo rezimirati. Ne, ovo nisu rezultati proučavanja SQL-a, već rezultati vrijednosti stupaca tablica baze podataka. SQL agregatne funkcije rade na vrijednostima stupca kako bi proizvele jednu rezultirajuću vrijednost. Najčešće korištene SQL agregatne funkcije su SUM, MIN, MAX, AVG i COUNT. Treba razlikovati dvije primjene agregatne funkcije. Prvo, agregatne funkcije koriste se same i vraćaju jednu rezultirajuću vrijednost. Drugo, agregatne funkcije se koriste s klauzulom SQL GROUP BY, odnosno grupiranjem po poljima (stupcima) kako bi se dobile rezultirajuće vrijednosti u svakoj grupi. Razmotrimo prvo slučajeve korištenja agregatnih funkcija bez grupiranja.
SQL funkcija SUM
SQL funkcija SUM vraća zbroj vrijednosti u stupcu tablice baze podataka. Može se primijeniti samo na stupce čije su vrijednosti brojevi. SQL upiti da biste dobili rezultirajući zbroj počnite ovako:
SELECT SUM (COLUMN_NAME) ...
Nakon ovog izraza slijedi FROM (TABLE_NAME), a zatim se uvjet može navesti korištenjem WHERE klauzule. Osim toga, nazivu stupca može prethoditi DISTINCT, što znači da će se brojati samo jedinstvene vrijednosti. Prema zadanim postavkama, sve vrijednosti se uzimaju u obzir (za ovo možete posebno navesti ne DISTINCT, već ALL, ali riječ ALL nije potrebna).
Ako želite pokrenuti upite baze podataka iz ovog vodiča u MS-u SQL poslužitelj, ali ovaj DBMS nije instaliran na vašem računalu, tada ga možete instalirati koristeći upute na ovoj poveznici .
Prvo ćemo raditi s bazom podataka poduzeća - Company1. Skripta za izradu ove baze podataka, njezinih tablica i popunjavanje tablica podacima nalazi se u datoteci na ovoj poveznici .
Primjer 1. Postoji baza podataka poduzeća s podacima o njegovim odjelima i zaposlenicima. Tablica osoblja također ima stupac s podacima o plaćama zaposlenika. Odabir iz tablice izgleda ovako (za povećanje slike kliknite na nju lijevom tipkom miša):
Za dobivanje zbroja svih plaća koristimo sljedeći upit (na MS SQL Serveru - s prethodnom konstrukcijom USE company1;):
SELECT SUM (Plaća) FROM osoblja
Ovaj će upit vratiti vrijednost 287664,63.
A sada . Na vježbama već počinjemo komplicirati zadatke, približavajući ih onima koji se susreću u praksi.
SQL MIN funkcija
Funkcija SQL MIN također radi na stupcima čije su vrijednosti brojevi i vraća najmanju vrijednost od svih vrijednosti u stupcu. Ova funkcija ima sintaksu sličnu onoj funkcije SUM.
Primjer 3. Baza podataka i tablica su iste kao u primjeru 1.
Moramo saznati minimalnu plaću za zaposlenike odjela broj 42. Da biste to učinili, napišite sljedeći upit (na MS SQL Serveru - s prefiksom USE company1;):
Upit će vratiti vrijednost 10505,90.
I opet vježba za samorješenje. U ovoj i nekim drugim vježbama bit će vam potrebna ne samo tablica Staff, već i tablica Org koja sadrži podatke o odjelima tvrtke:
Primjer 4. Tablica Org dodaje se tablici Staff koja sadrži podatke o odjelima tvrtke. Ispišite minimalni broj godina rada jednog zaposlenika u odjelu koji se nalazi u Bostonu.
SQL MAX funkcija
Funkcija SQL MAX radi slično i ima sličnu sintaksu, koja se koristi kada trebate odrediti najveću vrijednost među svim vrijednostima u stupcu.
Primjer 5.
Moramo saznati maksimalnu plaću zaposlenika u odjelu broj 42. Da biste to učinili, napišite sljedeći upit (na MS SQL Serveru - s prefiksom USE company1;):
Upit će vratiti vrijednost 18352,80
Vrijeme je vježbe za samostalno rješavanje.
Primjer 6. Opet radimo s dvije tablice - Staff i Org. Prikažite naziv odjela i najveću vrijednost provizije koju prima jedan zaposlenik u odjelu koji pripada skupini odjela (Division) Istočni. Koristiti JOIN (spajanje tablica) .
SQL AVG funkcija
Ono što je navedeno u vezi sa sintaksom za prethodno opisane funkcije vrijedi i za funkciju SQL AVG. Ova funkcija vraća prosjek svih vrijednosti u stupcu.
Primjer 7. Baza podataka i tablica su iste kao u prethodnim primjerima.
Recimo da želite saznati prosječni radni staž zaposlenika u odjelu broj 42. Da biste to učinili, napišite sljedeći upit (na MS SQL Serveru - s prethodnom konstrukcijom USE company1;):
Rezultat će biti 6,33
Primjer 8. Radimo za jednim stolom - Staff. Prikaz prosječne plaće zaposlenika s 4 do 6 godina iskustva.
SQL COUNT funkcija
Funkcija SQL COUNT vraća broj zapisa u tablici baze podataka. Ako u upitu navedete SELECT COUNT(COLUMN_NAME) ..., rezultat će biti broj zapisa bez uzimanja u obzir onih zapisa u kojima je vrijednost stupca NULL (nedefinirano). Ako koristite zvjezdicu kao argument i pokrenete SELECT COUNT(*) ... upit, rezultat će biti broj svih zapisa (redaka) tablice.
Primjer 9. Baza podataka i tablica su iste kao u prethodnim primjerima.
Želite znati broj svih zaposlenika koji primaju provizije. Broj zaposlenika čije vrijednosti stupca Comm nisu NULL vratit će sljedeći upit (na MS SQL Serveru - s prefiksom USE company1;):
SELECT COUNT (Comm) FROM Staff
Rezultat će biti 11.
Primjer 10. Baza podataka i tablica su iste kao u prethodnim primjerima.
Ako želite saznati ukupan broj zapisa u tablici, tada upotrijebite upit sa zvjezdicom kao argument funkcije COUNT (na MS SQL Serveru - s prethodnom konstrukcijom USE company1;):
SELECT COUNT (*) FROM Staff
Rezultat će biti 17.
U sljedećem vježba za samostalno rješavanje morat ćete koristiti podupit.
Primjer 11. Radimo za jednim stolom - Staff. Prikaz broja zaposlenih u odjelu planiranja (Plains).
Skupne funkcije sa SQL GROUP BY
Pogledajmo sada korištenje agregatnih funkcija zajedno s SQL naredbom GROUP BY. Naredba SQL GROUP BY koristi se za grupiranje vrijednosti rezultata po stupcima u tablici baze podataka. Web stranica ima lekcija posvećena posebno ovom operatoru .
Radit ćemo s bazom podataka "Ads Portal 1". Skripta za izradu ove baze podataka, njezine tablice i popunjavanje tablice podataka nalazi se u datoteci na ovoj poveznici .
Primjer 12. Dakle, postoji baza podataka portala za oglase. Ima tablicu oglasa koja sadrži podatke o oglasima poslanim za tjedan. Stupac Kategorija sadrži podatke o velikim kategorijama oglasa (npr. Nekretnine), a stupac Dijelovi sadrži podatke o manjim dijelovima koji su uključeni u kategorije (npr. dijelovi Apartmani i Ljetnikovi dijelovi su kategorije Nekretnine). U stupcu Jedinice nalaze se podaci o broju zaprimljenih oglasa, a u stupcu Novac podaci o novčanom iznosu primljenom za podnošenje oglasa.
| Kategorija | Dio | Jedinice | Novac |
| Prijevoz | Automobili | 110 | 17600 |
| Nekretnina | Apartmani | 89 | 18690 |
| Nekretnina | Dače | 57 | 11970 |
| Prijevoz | Motocikli | 131 | 20960 |
| Građevinski materijali | Daske | 68 | 7140 |
| Elektrotehnika | televizori | 127 | 8255 |
| Elektrotehnika | Hladnjaci | 137 | 8905 |
| Građevinski materijali | Regips | 112 | 11760 |
| Slobodno vrijeme | knjige | 96 | 6240 |
| Nekretnina | Kod kuće | 47 | 9870 |
| Slobodno vrijeme | glazba, muzika | 117 | 7605 |
| Slobodno vrijeme | Igre | 41 | 2665 |
Pomoću SQL naredbe GROUP BY pronađite iznos novca zarađen objavljivanjem oglasa u svakoj kategoriji. Pišemo sljedeći upit (na MS SQL Serveru - s prethodnom konstrukcijom USE adportal1;):
ODABERITE kategoriju, SUM (novac) KAO novac IZ GRUPE OGLASA PO kategoriji
Primjer 13. Baza podataka i tablica su iste kao u prethodnom primjeru.
Pomoću SQL naredbe GROUP BY saznajte u kojem je dijelu svake kategorije podnesak napravljen najveći broj reklame Pišemo sljedeći upit (na MS SQL Serveru - s prethodnom konstrukcijom USE adportal1;):
ODABERITE kategoriju, dio, MAX (jedinice) KAO maksimum IZ GRUPE OGLASA PO kategoriji
Rezultat će biti sljedeća tablica:
Ukupne i pojedinačne vrijednosti mogu se dobiti u jednoj tablici kombiniranje rezultata upita korištenjem operatora UNION .
Relacijske baze podataka i SQL jezik
Kako mogu saznati broj modela računala koje je proizveo određeni dobavljač? Kako odrediti prosječnu cijenu računala koja imaju iste tehnički podaci? Na ova i mnoga druga pitanja vezana uz neke statističke informacije može se odgovoriti pomoću finalne (agregatne) funkcije. Norma pruža sljedeće agregatne funkcije:
Sve te funkcije vraćaju jednu vrijednost. Istodobno, funkcije BROJ, MIN I MAKS primjenjiv na bilo koju vrstu podataka, dok IZNOS I PROSJ koriste se samo za numerička polja. Razlika između funkcije RAČUNATI(*) I RAČUNATI(<имя поля>) je da drugi ne uzima u obzir NULL vrijednosti prilikom izračuna.
Primjer. Pronađite minimalnu i maksimalnu cijenu za osobna računala:
Primjer. Pronađite raspoloživi broj računala proizvođača A:
Primjer. Ako nas zanima količina razni modeli, proizveden od strane proizvođača A, tada se upit može formulirati na sljedeći način (koristeći činjenicu da se u tablici proizvoda svaki model bilježi jednom):
Primjer. Pronađite broj dostupnih različitih modela koje proizvodi proizvođač A. Upit je sličan prethodnom, u kojem je trebalo odrediti ukupan broj modela koje proizvodi proizvođač A. Ovdje također trebate pronaći broj različitih modela u PC stol (tj. oni dostupni za prodaju).
Kako bi se osiguralo da se koriste samo jedinstvene vrijednosti prilikom dobivanja statističkih pokazatelja, kada argument agregatnih funkcija može se koristiti DISTINCT parametar. Još parametar SVE je zadana vrijednost i pretpostavlja da su sve vraćene vrijednosti u stupcu prebrojane. Operater,
Ako trebamo dobiti broj proizvedenih PC modela svatko proizvođača, morat ćete koristiti GROUP BY klauzula, sintaktički slijedeći WHERE odredbe.
GROUP BY klauzula
GROUP BY klauzula koristi se za definiranje grupa izlaznih linija na koje se može primijeniti agregatne funkcije (COUNT, MIN, MAX, AVG i SUM). Ako ova klauzula nedostaje, a koriste se agregatne funkcije, tada će svi stupci s imenima navedenim u IZABERI, moraju biti uključeni u agregatne funkcije, a te će se funkcije primijeniti na cijeli skup redaka koji zadovoljavaju predikat upita. Inače, svi stupci liste SELECT nije uključeno u skupnim funkcijama moraju biti navedene u klauzuli GROUP BY. Kao rezultat toga, svi retci izlaznih upita podijeljeni su u grupe koje karakteriziraju iste kombinacije vrijednosti u tim stupcima. Nakon toga, agregatne funkcije će se primijeniti na svaku grupu. Imajte na umu da se za GROUP BY sve NULL vrijednosti tretiraju kao jednake, tj. kod grupiranja prema polju koje sadrži NULL vrijednosti, svi takvi redovi će pasti u jednu grupu.Ako ako postoji klauzula GROUP BY, u klauzuli SELECT nema agregatnih funkcija, tada će upit jednostavno vratiti jedan redak iz svake grupe. Ova značajka, zajedno s ključnom riječi DISTINCT, može se koristiti za uklanjanje duplih redaka u skupu rezultata.
Pogledajmo jednostavan primjer:
| SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price S PC-ja GROUP BY model; |
U ovom zahtjevu za svaki model računala utvrđuje se njihov broj i prosječna cijena. Svi redovi s istom vrijednošću modela čine grupu, a izlaz SELECT-a izračunava broj vrijednosti i prosječne vrijednosti cijene za svaku grupu. Rezultat upita bit će sljedeća tablica:
| model | Kol_model | Prosječna_cijena |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Kad bi SELECT imao stupac datuma, tada bi bilo moguće izračunati ove pokazatelje za svaki određeni datum. Da biste to učinili, trebate dodati datum kao stupac grupiranja, a zatim bi se agregatne funkcije izračunale za svaku kombinaciju vrijednosti (model-datum).
Postoji nekoliko specifičnih pravila za izvođenje agregatnih funkcija:
- Ako kao rezultat zahtjeva nema primljenih redaka(ili više od jednog redaka za danu grupu), tada nema izvornih podataka za izračun bilo koje agregatne funkcije. U tom će slučaju rezultat funkcija COUNT biti nula, a rezultat svih ostalih funkcija bit će NULL.
- Argument funkcija agregata ne može sama sadržavati agregatne funkcije(funkcija iz funkcije). Oni. u jednom upitu nemoguće je, recimo, dobiti maksimalnu prosječnu vrijednost.
- Rezultat izvršavanja funkcije COUNT je cijeli broj(CIJELI BROJ). Druge agregatne funkcije nasljeđuju tipove podataka vrijednosti koje obrađuju.
- Ako funkcija SUM daje rezultat koji je veći od maksimalne vrijednosti korištene vrste podataka, greška.
Dakle, ako zahtjev ne sadrži GROUP BY klauzule, To agregatne funkcije uključen u SELECT klauzula, izvršavaju se na svim rezultirajućim recima upita. Ako zahtjev sadrži GROUP BY klauzula, svaki skup redaka koji ima iste vrijednosti stupca ili grupe stupaca navedenih u GROUP BY klauzula, čini grupu, i agregatne funkcije izvode se za svaku skupinu posebno.
IMAJU ponudu
Ako WHERE klauzula definira predikat za filtriranje redaka, zatim IMAJU ponudu primjenjuje se nakon grupiranja za definiranje sličnog predikata koji filtrira grupe prema vrijednostima agregatne funkcije. Ova klauzula je potrebna za provjeru vrijednosti koje su dobivene korištenjem funkcija agregata ne iz pojedinačnih redaka izvora zapisa definiranog u FROM klauzula, i od grupe takvih linija. Stoga takav ček ne može biti sadržan u WHERE klauzula.
SQL - Lekcija 11. Ukupne funkcije, izračunati stupci i pogledi
Ukupne funkcije nazivaju se i statističke, agregatne ili funkcije zbroja. Ove funkcije obrađuju skup nizova za brojanje i vraćaju jednu vrijednost. Postoji samo pet takvih funkcija:- Funkcija AVG() vraća prosječnu vrijednost stupca.
- Funkcija COUNT() vraća broj redaka u stupcu.
- MAX() funkcija vraća najveću vrijednost u stupcu.
- MIN() funkcija vraća najviše mala vrijednost u koloni.
- SUM() Funkcija vraća zbroj vrijednosti stupca.
SELECT MIN(cijena), MAX(cijena), ASOR(cijena) FROM cijene;
Sada želimo saznati koliko nam je robe donio dobavljač "Tiskara" (id=2). Podnijeti takav zahtjev nije tako jednostavno. Razmislimo kako to sastaviti:
1. Najprije iz tablice Supplies (incoming) odaberite identifikatore (id_incoming) onih isporuka koje je izvršio dobavljač "Tiskara" (id=2):
2. Sada iz tablice Dnevnik nabave (magazine_incoming) trebate odabrati robu (id_product) i njezine količine (quantity), koje su izvršene u isporukama koje se nalaze u točki 1. Odnosno, upit iz točke 1 postaje ugniježđen:
3. Sada u dobivenu tablicu trebamo dodati cijene za pronađene proizvode, koje su pohranjene u tablici Cijene. Odnosno, morat ćemo spojiti tablice Supply Magazine (magazine_incoming) i Prices pomoću stupca id_product:
4. U dobivenoj tablici očito nedostaje stupac Iznos, tj izračunati stupac. Mogućnost stvaranja takvih stupaca omogućena je u MySQL-u. Da biste to učinili, samo trebate navesti u upitu naziv izračunatog stupca i što bi trebao izračunati. U našem primjeru, takav će se stupac zvati zbroj, a izračunat će umnožak stupaca količine i cijene. Naziv novog stupca odvaja se riječju AS:
SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS zbroj FROM magazine_incoming, cijene WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
5. Super, ostaje nam samo zbrojiti stupac suma i na kraju saznati za koliko nam je dobavljač “Tiskara” donio robu. Sintaksa za korištenje funkcije SUM() je sljedeća:
SELECT SUM(column_name) FROM table_name;
Znamo naziv stupca - summa, ali nemamo naziv tablice, jer je rezultat upita. Što uraditi? Za takve slučajeve MySQL ima Views. Pogled je odabirni upit koji dobiva jedinstveni naziv i može se pohraniti u bazu podataka za kasniju upotrebu.
Sintaksa za stvaranje prikaza je sljedeća:
CREATE VIEW view_name AS zahtjev;
Spremimo naš zahtjev kao pogled pod nazivom report_vendor:
Stvori prikaz report_vendor kao odabrani magazine_incoming.id_product, magazine_incoming.quantity, cijene.price, magazine_incoming.quantity*cijene.Price kao summa iz magazine_incoming, cijene gdje magazine_incoming.Id_Product = cijene.id_incoming i idvend = odabir idvend = odabir );
6. Sada možete koristiti konačnu funkciju SUM():
SELECT SUM(suma) FROM report_vendor;
Tako smo postigli rezultat, iako smo za to morali koristiti ugniježđene upite, spojeve, izračunate stupce i prikaze. Da, ponekad morate razmišljati da biste postigli rezultat, bez toga ne možete nigdje stići. Ali dotakli smo se dvije vrlo važne teme - izračunatih stupaca i prikaza. Razgovarajmo o njima detaljnije.
Izračunata polja (stupci)
Koristeći primjer, danas smo pogledali matematičko izračunato polje. Ovdje bih želio dodati da možete koristiti ne samo operaciju množenja (*), već i oduzimanje (-), zbrajanje (+) i dijeljenje (/). Sintaksa je sljedeća:SELECT column_name 1, column_name 2, column_name 1 * column_name 2 AS izračunato_ime_stupca FROM table_name;
Druga nijansa je ključna riječ AS, koristili smo je za postavljanje naziva izračunatog stupca. Zapravo, ova se ključna riječ koristi za postavljanje aliasa za bilo koje stupce. Zašto je to potrebno? Za smanjenje koda i čitljivost. Na primjer, naš pogled može izgledati ovako:
CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS zbroj FROM magazine_incoming AS A, cijene AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_dobavljač=2);
Složite se da je ovo puno kraće i jasnije.
Reprezentacija
Već smo pogledali sintaksu za stvaranje pogleda. Jednom kada su prikazi stvoreni, mogu se koristiti na isti način kao i tablice. Odnosno, pokrenite upite prema njima, filtrirajte i sortirajte podatke i kombinirajte neke prikaze s drugima. S jedne strane, ovo je vrlo prikladan način za pohranu često korištenih složenih upita (kao u našem primjeru).No zapamtite da pogledi nisu tablice, odnosno ne pohranjuju podatke, već ih samo dohvaćaju iz drugih tablica. Dakle, prvo, kada se podaci u tablicama promijene, promijenit će se i rezultati prezentacije. I drugo, kada se postavi zahtjev prema pogledu, traženi podaci se traže, odnosno smanjuje se izvedba DBMS-a. Stoga ih ne biste trebali zlorabiti.
Opisuje korištenje aritmetičkih operatora i konstrukciju izračunatih stupaca. Razmatraju se konačne (agregatne) funkcije COUNT, SUM, AVG, MAX, MIN. Pruža primjer korištenja operatora GROUP BY za grupiranje u upitima za odabir podataka. Opisuje upotrebu klauzule HAVING.Izgradnja izračunatih polja
U opći slučaj za stvaranje izračunato (izvedeno) polje lista SELECT mora sadržavati neki SQL izraz. Ovi izrazi koriste aritmetičke operacije zbrajanje, oduzimanje, množenje i dijeljenje, kao i ugrađene funkcije SQL jezika. Možete navesti naziv bilo kojeg stupca (polja) tablice ili upita, ali koristite samo naziv stupca tablice ili upita koji je naveden na popisu FROM klauzule odgovarajuće izjave. Prilikom konstruiranja složenih izraza mogu biti potrebne zagrade.
SQL standardi omogućuju vam eksplicitno navođenje imena stupaca rezultirajuće tablice, za koje se koristi klauzula AS.
SELECT Product.Name, Product.Price, Deal.Quantity, Product.Price*Deal.Quantity AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode Primjer 6.1. Izračun ukupnog troška za svaku transakciju.
Primjer 6.2. Dobijte popis tvrtki s naznakom prezimena i inicijala klijenata.
ODABERITE tvrtku, prezime+""+ lijevo(ime,1)+"."+lijevo(srednje ime,1)+"."KAO puno ime od klijenta Primjer 6.2. Dobivanje popisa tvrtki s naznakom prezimena i inicijala klijenata.
Zahtjev koristi ugrađenu funkciju Lijevo, koja vam u ovom slučaju omogućuje izrezivanje jednog znaka slijeva u tekstualnoj varijabli.
Primjer 6.3. Dobijte popis proizvoda s naznakom godine i mjeseca prodaje.
SELECT Product.Name, Year(Transaction.Date) AS Year, Month(Transaction.Date) AS Month FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode Primjer 6.3. Primanje popisa proizvoda s naznakom godine i mjeseca prodaje.
Upit koristi ugrađene funkcije Godina i Mjesec za izdvajanje godine i mjeseca iz datuma.
Korištenje funkcija sažetka
Pomoću finalne (agregatne) funkcije unutar SQL upita možete dobiti niz općih statističkih informacija o skupu odabranih vrijednosti izlaznog skupa.
Korisnik ima pristup sljedećim osnovnim konačne funkcije:
- Count (Expression) - određuje broj zapisa u izlaznom skupu SQL upita;
- Min/Max (Expression) - odredi najmanji i najveći skup vrijednosti u određenom polju zahtjeva;
- Prosjek (izraz) - ova vam funkcija omogućuje izračunavanje prosjeka skupa vrijednosti pohranjenih u određenom polju zapisa odabranih upitom. To je aritmetički prosjek, tj. zbroj vrijednosti podijeljen njihovim brojem.
- Zbroj (izraz) - izračunava zbroj skupa vrijednostisadržanih u određenom polju zapisa odabranih upitom.
Najčešće se nazivi stupaca koriste kao izrazi. Izraz se također može izračunati pomoću vrijednosti nekoliko tablica.
Sve ove funkcije rade na vrijednostima u jednom stupcu tablice ili na aritmetičkom izrazu i vraćaju jednu vrijednost. Funkcije COUNT, MIN i MAX primjenjuju se i na numerička i na nenumerička polja, dok se funkcije SUM i AVG mogu koristiti samo za numerička polja, s izuzetkom COUNT(*). Prilikom izračunavanja rezultata bilo koje funkcije, prvo se eliminiraju sve nulte vrijednosti, a zatim se zahtijevana operacija primjenjuje samo na preostale specifične vrijednosti stupca. Opcija COUNT(*) poseban je slučaj upotrebe funkcije COUNT; njezina je svrha prebrojati sve retke u rezultirajućoj tablici, bez obzira na to sadrži li null vrijednosti, duplikate ili bilo koje druge vrijednosti.
Ako trebate eliminirati duplicirane vrijednosti prije upotrebe funkcije sažetka, prije naziva stupca u definiciji funkcije morate staviti ključnu riječ DISTINCT. Nema značenje za funkcije MIN i MAX, ali njegova upotreba može utjecati na rezultate funkcija SUM i AVG, pa trebate razmotriti treba li biti prisutna u svakom slučaju. Osim toga, ključna riječ DISTINCT može se navesti samo jednom u bilo kojem upitu.
Vrlo je važno napomenuti da konačne funkcije može se koristiti samo na popisu u klauzuli SELECT i kao dio klauzule HAVING. U svim drugim slučajevima to je neprihvatljivo. Ako popis u klauzuli SELECT sadrži konačne funkcije, a tekst upita ne sadrži klauzulu GROUP BY, koja omogućuje kombiniranje podataka u grupe, tada niti jedan element popisa klauzule SELECT ne može sadržavati nikakve reference na polja, osim u situaciji kada polja djeluju kao argumenti konačne funkcije.
Primjer 6.4. Odredite prvi abecedni naziv proizvoda.
SELECT Min(Product.Name) AS Min_Name FROM Product Primjer 6.4. Određivanje prvog abecednog naziva proizvoda.
Primjer 6.5. Odredite broj transakcija.
SELECT Count(*) AS Number_of_deals FROM Deal Primjer 6.5. Odredite broj transakcija.
Primjer 6.6. Odredite ukupnu količinu prodane robe.
SELECT Sum(Deal.Quantity) AS Item_Quantity FROM Deal Primjer 6.6. Utvrđivanje ukupne količine prodane robe.
Primjer 6.7. Odredite prosječnu cijenu prodane robe.
SELECT Prosj.(Product.Price) AS Prosj.cijena FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode; Primjer 6.7. Određivanje prosječne cijene prodane robe.
SELECT Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode Primjer 6.8. Izračunavanje ukupne cijene prodane robe.
GROUP BY klauzula
Upiti često zahtijevaju generiranje međuzbrojeva, što je obično označeno pojavom fraze "za svaki..." u upitu. U tu svrhu u SELECT izjava Koristi se klauzula GROUP BY. Upit koji sadrži GROUP BY naziva se upit za grupiranje jer grupira podatke koje vraća operacija SELECT, a zatim stvara jedan redak sažetka za svaku pojedinačnu grupu. SQL standard zahtijeva da klauzula SELECT i klauzula GROUP BY budu blisko povezane. Kada izjava SELECT sadrži klauzulu GROUP BY, svaki element popisa u klauzuli SELECT mora imati jednu vrijednost za cijelu grupu. Štoviše, klauzula SELECT može uključivati samo sljedeće vrste elementi: imena polja, konačne funkcije, konstante i izrazi koji uključuju kombinacije gore navedenih elemenata.
Sva imena polja navedena u klauzuli SELECT također se moraju pojaviti u klauzuli GROUP BY - osim ako se naziv stupca ne koristi u konačna funkcija. Obrnuto pravilo nije točno - klauzula GROUP BY može sadržavati nazive stupaca koji nisu na popisu klauzule SELECT.
Ako se klauzula WHERE koristi zajedno s GROUP BY, ona se prva obrađuje i grupiraju se samo oni redovi koji zadovoljavaju uvjet pretraživanja.
SQL standard određuje da se pri grupiranju sve vrijednosti koje nedostaju tretiraju kao jednake. Ako dva retka tablice u istom stupcu grupiranja sadrže NULL vrijednost i identične vrijednosti u svim drugim stupcima grupiranja koji nisu nulti, oni se smještaju u istu grupu.
Primjer 6.9. Izračunajte prosječni obujam kupnje svakog kupca.
SELECT Client.LastName, Avg(Transaction.Quantity) AS Average_Quantity FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.LastName Primjer 6.9. Izračunajte prosječni obujam kupnje svakog kupca.
Izraz "svaki kupac" odražava se u SQL upitu u obliku rečenice GROUP BY Client.LastName.
Primjer 6.10. Odredite za koliko je svaki proizvod prodan.
SELECT Product.Name, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name Primjer 6.10. Određivanje iznosa za koji je svaki proizvod prodan.
SELECT Client.Company, Count(Transaction.TransactionCode) AS Number_of_transactions FROM Client INNER JOIN Transaction ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company Primjer 6.11. Brojanje broja transakcija koje je izvršila svaka tvrtka.
SELECT Customer.Company, Sum(Transaction.Quantity) AS Total_Quantity, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN (Customer INNER JOIN Transaction ON Customer.ClientCode=Transaction.CustomerCode) ON Product.ProductCode=Transakcija .Šifra proizvoda GROUP BY Client.Company Primjer 6.12. Izračun ukupne količine robe kupljene za svaku tvrtku i njen trošak.
Primjer 6.13. Odredite ukupnu cijenu svakog proizvoda za svaki mjesec.
SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name, Month(Transaction.Date) ) Primjer 6.13. Određivanje ukupne cijene svakog proizvoda za svaki mjesec.
Primjer 6.14. Odredite ukupne troškove svakog prvoklasnog proizvoda za svaki mjesec.
SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode WHERE Product.Grade="First" GROUP BY Product .Ime, Mjesec (Datum.Transakcije) Primjer 6.14. Određivanje ukupne cijene svakog prvoklasnog proizvoda za svaki mjesec.
IMAJU ponudu
Korištenjem HAVING-a odražavaju se svi blokovi podataka prethodno grupirani pomoću GROUP BY koji zadovoljavaju uvjete navedene u HAVING-u. Ovaj dodatna prilika"filter" izlaznog skupa.
Uvjeti u HAVING razlikuju se od uvjeta u WHERE:
- HAVING isključuje grupe s rezultatima agregirane vrijednosti iz dobivenog skupa podataka;
- WHERE isključuje zapise koji ne zadovoljavaju uvjet iz izračuna agregatnih vrijednosti grupiranjem;
- Skupne funkcije ne mogu se navesti u uvjetu pretraživanja WHERE.
Primjer 6.15. Identificirajte tvrtke čiji je ukupan broj transakcija premašio tri.
SELECT Client.Company, Count(Trade.Quantity) AS Number_of_deals FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company HAVING Count(Transaction.Quantity)>3 Primjer 6.15. Identifikacija tvrtki čiji je ukupan broj transakcija premašio tri.
Primjer 6.16. Prikažite popis robe prodane za više od 10.000 rubalja.
SELECT Product.Name, Sum(Product.Price*Deal.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Deal.Quantity)>10000 Primjer 6.16. Prikaz popisa robe prodane za više od 10.000 rubalja.
Primjer 6.17. Prikažite popis proizvoda prodanih za više od 10.000 bez navođenja iznosa.
SELECT Product.Name FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Transaction.Quantity)>10000 Primjer 6.17. Prikažite popis proizvoda prodanih za više od 10.000 bez navođenja iznosa.
Funkcija SUM u SQL jeziku, unatoč svojoj jednostavnosti, često se koristi pri radu s bazom podataka. Uz njegovu pomoć prikladno je dobiti neke srednje ili konačne rezultate bez pribjegavanja pomoći pomoćnih DBMS alata.
Sintaksa funkcije
U većini jezika SQL sintaksa zbroj je isti - kao argument se koristi samo naziv polja ili neka aritmetička operacija više njih, preko kojih se traži zbroj.
U iznimnim slučajevima moguće je prenijeti određenu vrijednost kao broj ili varijablu, ali se takve "sheme" praktički ne koriste, jer nemaju veliku vrijednost. Ispod je sintaksa funkcije u SQL-u:
sum(a) - ovdje se kao parametar a koristi neka numerička vrijednost ili izraz
Vrijedno je napomenuti da prije parametra možete postaviti ključne riječi, na primjer DISTINCT ili ALL, koji će uzeti samo jedinstvene ili sve vrijednosti.
Primjer korištenja SUM-a u SQL-u
Da biste u potpunosti razumjeli kako funkcija radi, vrijedi razmotriti nekoliko primjera. U SQL-u, SUM se može koristiti i kao povratni rezultat i kao međuvrijednost, na primjer, za testiranje uvjeta.
Za prvi slučaj razmotrite opciju kada trebate vratiti iznos prodaje za svaki proizvod, uzimajući u obzir da broj obavljenih kupnji može biti u množini. Da biste dobili rezultat, bit će dovoljno pokrenuti sljedeći upit:
SELECT Product, sum(PurchaseAmount) FROM Sales GroupBy Product;
Kao odgovor na ovu naredbu postojat će jedinstveni popis proizvoda s ukupnim iznosom kupnje za svaki od njih.
Za drugi primjer, trebate dobiti popis proizvoda čiji je iznos prodaje premašio određenu vrijednost, na primjer, 100. Rezultat za ovaj zadatak možete dobiti na nekoliko načina, od kojih je najoptimalniji izvršiti jedan zahtjev:
SELECT Product FROM (SELECT Product, sum (Purchase Amount) as Amount FROM Sales) WHERE Sum > 100.