Mācīsimies rezumēt. Nē, tie nav SQL apguves rezultāti, bet gan datu bāzes tabulu kolonnu vērtību rezultāti. SQL apkopotās funkcijas darbojas uz kolonnas vērtībām, lai iegūtu vienu rezultāta vērtību. Visbiežāk izmantotās SQL apkopošanas funkcijas ir SUM, MIN, MAX, AVG un COUNT. Ir divi gadījumi, kad jāizmanto apkopotās funkcijas. Pirmkārt, apkopotās funkcijas tiek izmantotas pašas un atgriež vienu rezultāta vērtību. Otrkārt, apkopotās funkcijas tiek izmantotas ar klauzulu SQL GROUP BY, tas ir, ar grupēšanu pēc laukiem (kolonnām), lai iegūtu iegūtās vērtības katrā grupā. Vispirms apsveriet gadījumus, kad tiek izmantotas apkopotās funkcijas bez grupēšanas.

SQL SUM funkcija

Funkcija SQL SUM atgriež datu bāzes tabulas kolonnas vērtību summu. To var lietot tikai kolonnām, kuru vērtības ir skaitļi. SQL vaicājumi Lai iegūtu iegūto summu, sāciet šādi:

ATLASĪT SUMMU (COLUMNAME)...

Šai izteiksmei seko FROM (TABLE_NAME), un tad nosacījumu var norādīt, izmantojot WHERE klauzulu. Turklāt DISTINCT var pievienot kolonnas nosaukumam, lai norādītu, ka tiks ņemtas vērā tikai unikālas vērtības. Pēc noklusējuma tiek ņemtas vērā visas vērtības (šim nolūkam jūs varat īpaši norādīt nevis DISTINCT, bet ALL, bet vārds ALL nav obligāts).

Ja vēlaties palaist datu bāzes vaicājumus no šīs nodarbības uz MS SQL Server, bet šī DBVS nav instalēta jūsu datorā, varat to instalēt, izmantojot norādījumus šajā saitē. .

Pirmkārt, strādāsim ar uzņēmuma datu bāzi – Uzņēmums1. Šīs datu bāzes izveides skripts, tās tabulas un tabulu aizpildīšana ar datiem ir failā uz šīs saites .

1. piemērs Ir uzņēmuma datu bāze ar datiem par tā nodaļām un darbiniekiem. Personāla tabulā ir arī kolonna ar darbinieku algu datiem. Izvēlei no tabulas ir šāda forma (lai palielinātu attēlu, noklikšķiniet uz tās ar peles kreiso taustiņu):

Lai iegūtu visu algu summu, mēs izmantojam šādu vaicājumu (MS SQL Server - ar prefiksu USE company1;):

IZVĒLĒTIES SUMMU (Algu) NO personāla

Šis vaicājums atgriezīs vērtību 287664.63.

Un tagad . Vingrojumos jau sākam sarežģīt uzdevumus, tuvinot tos tiem, ar kuriem saskaras praksē.

SQL MIN funkcija

Funkcija SQL MIN darbojas arī kolonnās, kuru vērtības ir skaitļi, un atgriež visu kolonnas vērtību minimumu. Šīs funkcijas sintakse ir līdzīga funkcijai SUM.

3. piemērs Datubāze un tabula ir tādas pašas kā 1. piemērā.

Nepieciešams noskaidrot 42. nodaļas darbinieku minimālo algu. Lai to izdarītu, mēs rakstām šādu vaicājumu (uz MS SQL Server - ar prefiksu USE company1;):

Vaicājums atgriezīs vērtību 10505.90.

Un atkal vingrinājums priekš neatkarīgs risinājums . Šajā un dažos citos uzdevumos jums būs nepieciešama ne tikai personāla tabula, bet arī Org tabula, kurā ir dati par uzņēmuma nodaļām:

4. piemērs Tabula Org tiek pievienota tabulai Personāls, kurā ir dati par uzņēmuma nodaļām. Norādiet minimālo gadu skaitu, ko viens darbinieks ir nostrādājis departamentā, kas atrodas Bostonā.

SQL MAX funkcija

Funkcija SQL MAX darbojas līdzīgi un tai ir līdzīga sintakse, kas tiek izmantota, ja vēlaties noteikt maksimālo vērtību starp visām kolonnas vērtībām.

5. piemērs

Nepieciešams noskaidrot 42. nodaļas darbinieku maksimālo algu. Lai to izdarītu, mēs rakstām šādu vaicājumu (uz MS SQL Server - ar prefiksu USE company1;):

Vaicājums atgriezīs vērtību 18352.80

Ir laiks pašnoteikšanās vingrinājumi.

6. piemērs Atkal mēs strādājam ar diviem galdiem - Staff un Org. Parādīt nodaļas nosaukumu un maksimālo komisiju summu, ko saņēmis viens darbinieks nodaļā, kas ietilpst nodaļu grupā (Division) Eastern. Izmantot JOIN (savieno tabulas) .

SQL AVG funkcija

Tas, kas tika teikts par iepriekš aprakstīto funkciju sintaksi, attiecas arī uz SQL AVG funkciju. Šī funkcija atgriež vidējo vērtību no visām kolonnā esošajām vērtībām.

7. piemērs Datubāze un tabula ir tādas pašas kā iepriekšējos piemēros.

Lai būtu jānoskaidro 42. nodaļas darbinieku vidējais darba stāžs. Lai to izdarītu, rakstām šādu vaicājumu (MS SQL Server - ar prefiksu USE company1;):

Rezultāts būs 6.33

8. piemērs Strādājam ar vienu galdu - Personāls. Parādiet vidējo algu darbiniekiem ar pieredzi no 4 līdz 6 gadiem.

SQL COUNT funkcija

Funkcija SQL COUNT atgriež ierakstu skaitu datu bāzes tabulā. Ja vaicājumā norādāt SELECT COUNT(COLUMNAME) ..., tad rezultāts būs ierakstu skaits, neņemot vērā tos ierakstus, kuros kolonnas vērtība ir NULL (nenodefinēta). Ja kā argumentu izmantojat zvaigznīti un sāciet ATLASĪT vaicājumu COUNT(*) ..., tad rezultāts būs visu tabulas ierakstu (rindu) skaits.

9. piemērs Datubāze un tabula ir tādas pašas kā iepriekšējos piemēros.

Jūs vēlaties uzzināt visu darbinieku skaitu, kuri saņem komisijas maksas. Darbinieku skaits, kuru Comm kolonnas vērtības nav NULL, atgriezīs šādu vaicājumu (MS SQL Server — ar USE uzņēmumu1; priekšā esoša konstrukcija):

IZVĒLĒTIES SKAITS (Comm) NO personāla

Rezultātā tiks iegūta vērtība 11.

10. piemērs Datubāze un tabula ir tādas pašas kā iepriekšējos piemēros.

Ja jums ir jānoskaidro kopējais ierakstu skaits tabulā, mēs izmantojam vaicājumu ar zvaigznīti kā argumentu funkcijai COUNT (MS SQL Server - ar prefiksu USE company1;):

IZVĒLĒTIES SKAITS (*) NO darbiniekiem

Rezultātā tiks iegūta vērtība 17.

Nākamais pašnoteikšanās vingrinājums jums ir jāizmanto apakšvaicājums.

11. piemērs. Strādājam ar vienu galdu - Personāls. Parādiet darbinieku skaitu Plains departamentā.

Apkopot funkcijas ar SQL GROUP BY

Tagad apskatīsim apkopoto funkciju izmantošanu kopā ar klauzulu SQL GROUP BY. Klauzulu SQL GROUP BY izmanto, lai datu bāzes tabulā grupētu iegūtās vērtības pēc kolonnām. Vietnē ir nodarbība, kas veltīta šim operatoram atsevišķi .

Strādāsim ar datubāzi "Sludinājumu portāls 1". Šīs datu bāzes izveides, tās tabulas un datu tabulas aizpildīšanas skripts ir failā pie šīs saites .

12. piemērs. Tātad ir sludinājumu portāla datu bāze. Tajā ir reklāmu tabula, kurā ir dati par nedēļā iesniegtajām reklāmām. Slejā Kategorija ir dati par lielām reklāmu kategorijām (piemēram, Nekustamais īpašums), bet slejā Daļas ir dati par mazākām kategorijās iekļautajām daļām (piemēram, dzīvokļu un villu daļas ir kategorijas Nekustamais īpašums daļas). Kolonnā Vienības ir dati par iesniegto sludinājumu skaitu, bet slejā Nauda ir naudas summa, kas nopelnīta par sludinājumu iesniegšanu.

Kategorija	daļa	Vienības	Nauda
Transports	mehāniskie transportlīdzekļi	110	17600
Nekustamais īpašums	Dzīvokļi	89	18690
Nekustamais īpašums	Dachas	57	11970
Transports	Motocikli	131	20960
celtniecības materiāli	Dēļi	68	7140
elektrotehnika	televizori	127	8255
elektrotehnika	Ledusskapji	137	8905
celtniecības materiāli	Regips	112	11760
Atpūta	Grāmatas	96	6240
Nekustamais īpašums	Mājās	47	9870
Atpūta	Mūzika	117	7605
Atpūta	Spēles	41	2665

Izmantojot klauzulu SQL GROUP BY, atrodiet naudas summu, kas iegūta, iesniedzot reklāmas katrā kategorijā. Mēs rakstām šādu vaicājumu (uz MS SQL Server - ar prefiksu USE adportal1;):

IZVĒLĒTIES kategoriju, SUMMA (nauda) KĀ Naudu NO REKLĀMU GRUPAS PĒC kategorijas

13. piemērs Datubāze un tabula ir tādas pašas kā iepriekšējā piemērā.

Izmantojot klauzulu SQL GROUP BY, noskaidrojiet, kurā katras kategorijas daļā bija visvairāk reklāmu. Mēs rakstām šādu vaicājumu (uz MS SQL Server - ar prefiksu USE adportal1;):

ATLASĪT kategoriju, daļu, MAX (vienības) AS maksimumu NO REKLĀMU GRUPAS PĒC kategorijas

Rezultāts būs šāda tabula:

Kopējās un individuālās vērtības var iegūt vienā tabulā vaicājuma rezultātu apvienošana, izmantojot operatoru UNION .

Relāciju datu bāzes un SQL valoda

Apraksta aritmētisko operatoru lietošanu un aprēķināto kolonnu uzbūvi. Tiek aplūkotas kopsavilkuma (apkopošanas) funkcijas COUNT, SUM, AVG, MAX, MIN. Tiek sniegts operatora GROUP BY izmantošanas piemērs datu atlases vaicājumu grupēšanai. Apraksta HAVING klauzulas izmantošanu.

Aprēķināto lauku veidošana

Vispār, lai radītu aprēķinātais (atvasinātais) lauks sarakstā SELECT ir jānorāda kāda SQL valodas izteiksme. Šīs izteiksmes izmanto saskaitīšanas, atņemšanas, reizināšanas un dalīšanas aritmētiskās darbības, kā arī SQL valodas iebūvētās funkcijas. Varat norādīt jebkuras tabulas vai vaicājuma kolonnas (lauka) nosaukumu, taču izmantojiet tikai tās tabulas vai vaicājuma kolonnas nosaukumu, kas ir norādīts attiecīgā priekšraksta klauzulā FROM. Iekavas var būt nepieciešamas, veidojot sarežģītas izteiksmes.

SQL standarti ļauj skaidri norādīt iegūtās tabulas kolonnu nosaukumus, kurām tiek izmantota AS frāze.

ATLASĪT Preces.nosaukums, preces.cena, darījums.daudzums, preces.cena*tirdzniecība.daudzums kā izmaksas no preces IEKŠĒJĀ PIEVIENOTIES Tirdzniecība ON Item.ItemCode=Trade.ItemID Piemērs 6.1. Katra darījuma kopējo izmaksu aprēķins.

Piemērs 6.2. Iegūstiet uzņēmumu sarakstu ar klientu vārdiem un iniciāļiem.

ATLASĪT uzņēmumu, uzvārds+""+ Kreisais(vārds,1)+"."+Kreisais(patronīms,1)+"."AS Pilns vārds NO klienta Piemērs 6.2. Uzņēmumu saraksta iegūšana, norādot klientu vārdus un iniciāļus.

Vaicājumā tiek izmantota iebūvētā funkcija Left , kas šajā gadījumā ļauj teksta mainīgajā izgriezt vienu rakstzīmi no kreisās puses.

Piemērs 6.3. Saņemiet produktu sarakstu ar pārdošanas gadu un mēnesi.

SELECT Item.Name, Year(Trade.Date) AS Gads, Mēnesis(Tirdzniecības datums) AS Mēnesis NO preces IEKŠĒJĀ JOIN Trade ON Item.ItemID=Trade.ItemID Piemērs 6.3. Preču saraksta iegūšana ar pārdošanas gadu un mēnesi.

Vaicājumā tiek izmantotas iebūvētās gada un mēneša funkcijas, lai no datuma iegūtu gadu un mēnesi.

Kopsavilkuma funkciju izmantošana

Izmantojot kopsavilkuma (apkopošanas) funkcijas SQL vaicājumā varat iegūt virkni vispārinošu statistisku informāciju par izvades kopas atlasīto vērtību kopu.

Lietotājam ir piekļuve tālāk norādītajam kopsavilkuma funkcijas:

• Skaits (Izteiksme) - nosaka ierakstu skaitu SQL vaicājuma izvades kopā;
• Min / Max (Izteiksme) - noteikt mazāko un lielāko no vērtību kopas noteiktā pieprasījuma laukā;
• Avg (Izteiksme) - šī funkcija ļauj aprēķināt vērtību kopas vidējo vērtību, kas saglabāta noteiktā vaicājumā atlasīto ierakstu laukā. Tas ir vidējais aritmētiskais, t.i. vērtību summa, kas dalīta ar to skaitu.
• Summa (Izteiksme) - aprēķina vērtību kopas summu, kas atrodas noteiktā vaicājumā atlasīto ierakstu laukā.

Visbiežāk kolonnu nosaukumi darbojas kā izteiksmes. Izteiksmi var novērtēt arī pēc vairāku tabulu vērtībām.

Visas šīs funkcijas darbojas ar vērtībām vienā tabulas kolonnā vai aritmētiskajā izteiksmē un atgriež vienu vērtību. Funkcijas COUNT , MIN un MAX attiecas gan uz ciparu, gan neciparu laukiem, savukārt funkcijas SUM un AVG var izmantot tikai ciparu lauku gadījumā, izņemot COUNT(*) . Aprēķinot jebkuru funkciju rezultātus, vispirms tiek izslēgtas visas nulles vērtības, pēc tam vajadzīgā darbība tiek piemērota tikai pārējām kolonnas konkrētajām vērtībām. Variants COUNT(*) ir īpašs funkcijas COUNT izmantošanas gadījums, tā mērķis ir saskaitīt visas rindas iegūtajā tabulā neatkarīgi no tā, vai tajās ir nulles, dublikāti vai citas vērtības.

Ja vēlaties novērst dublētās vērtības pirms vispārīgas funkcijas izmantošanas, pirms kolonnas nosaukuma funkcijas definīcijā ir jāievada atslēgvārds DISTINCT . Funkcijām MIN un MAX tam nav jēgas, taču tā lietošana var ietekmēt SUM un AVG funkciju rezultātus, tāpēc iepriekš jāapsver, vai tam vajadzētu būt katrā konkrētajā gadījumā. Turklāt DISTINCT atslēgvārdu var norādīt ne vairāk kā vienu reizi jebkurā vaicājumā.

Ir ļoti svarīgi to atzīmēt kopsavilkuma funkcijas var izmantot tikai SELECT klauzulu sarakstā un kā daļu no HAVING klauzulas. Visos citos gadījumos tas nav atļauts. Ja SELECT klauzulas saraksts satur kopsavilkuma funkcijas, un vaicājuma tekstā nav klauzulas GROUP BY, kas nodrošina datu grupēšanu, tad neviens no SELECT klauzulas saraksta elementiem nevar ietvert nekādas atsauces uz laukiem, izņemot gadījumu, kad lauki darbojas kā argumenti. galīgās funkcijas.

Piemērs 6.4. Nosakiet pirmā vienuma nosaukumu alfabētiskā secībā.

SELECT Min(Item.Name) AS Min_Name FROM Product Piemērs 6.4. Pirmā vienuma nosaukuma noteikšana alfabētiskā secībā.

Piemērs 6.5. Nosakiet darījumu skaitu.

SELECT Count(*) AS Number of_trades NO Tirdzniecības Piemērs 6.5. Nosakiet darījumu skaitu.

Piemērs 6.6. Nosakiet kopējo pārdoto preču daudzumu.

SELECT Sum(Trade.Quantity) AS Product_Daudzums NO Tirdzniecības Piemērs 6.6. Kopējā pārdoto preču daudzuma noteikšana.

Piemērs 6.7. Nosakiet pārdotās preces vidējo cenu.

SELECT Avg(Prece.Price) AS Vid. Piemērs 6.7. Pārdoto preču vidējās cenas noteikšana.

IZVĒLĒTIES Summa(prece.Cena*Tirdzniecība.Daudzums) KĀ izmaksas NO preces IEKŠĒJĀ PIEVIENOTIES Tirdzniecība ON Item.ItemID=Tirdzniecība.ItemID Piemērs 6.8. Pārdoto preču kopējo izmaksu aprēķins.

GROUP PĒC piedāvājuma

Vaicājumiem bieži ir jāģenerē starpsummas, ko vaicājumā parasti norāda ar frāzi "katram...". SELECT priekšraksts šim nolūkam izmanto klauzulu GROUP BY. Vaicājumu, kuram ir GROUP BY, sauc par grupēšanas vaicājumu, jo tas grupē datus no operācijas SELECT un pēc tam izveido vienu kopsavilkuma rindu katrai atsevišķai grupai. SQL standarts pieprasa, lai klauzula SELECT un klauzula GROUP BY būtu cieši saistītas. Ja priekšrakstā SELECT ir klauzula GROUP BY, katram saraksta vienumam klauzulā SELECT ir jābūt vienai vērtībai visai grupai. Turklāt klauzulā SELECT var būt ietverti tikai šāda veida elementi: lauku nosaukumi, kopsavilkuma funkcijas, konstantes un izteiksmes, kas ietver iepriekš minēto elementu kombinācijas.

Visiem lauku nosaukumiem, kas uzskaitīti klauzulā SELECT, ir jāparādās arī klauzulā GROUP BY, izņemot gadījumus, kad kolonnas nosaukums tiek izmantots galīgā funkcija. Apgrieztais noteikums nav patiess — klauzulā GROUP BY var būt kolonnu nosaukumi, kas nav iekļauti klauzulas SELECT sarakstā.

Ja WHERE klauzula tiek izmantota kopā ar GROUP BY , tad tā vispirms tiek apstrādāta un tiek grupētas tikai tās rindas, kas atbilst meklēšanas nosacījumiem.

SQL standarts nosaka, ka grupējot visas trūkstošās vērtības tiek uzskatītas par vienādām. Ja divās tabulas rindās ir NULL vienā grupēšanas kolonnā un identiskas vērtības visās pārējās grupēšanas kolonnās, kas nav tukšas, tās tiek ievietotas tajā pašā grupā.

Piemērs 6.9. Aprēķiniet katra klienta veikto pirkumu vidējo apjomu.

SELECT Client.LastName, Avg(Dal.Daudzums) AS Average_Quantity FROM Client INNER JOIN Transaction ON Client.ClientID=Transaction.ClientID GROUP BY BY Client.LastName Piemērs 6.9. Katra klienta veikto pirkumu vidējā apjoma aprēķins.

Frāze "no katra klienta" tiek atspoguļota SQL vaicājumā kā teikums GROUP BY Client.Uzvārds.

Piemērs 6.10. Nosakiet summu, par kādu tika pārdots katras preces produkts.

IZVĒLĒTIES Preces.Nosaukums, Summa(Prece.Cena*Tirdzniecība.Daudzums) KĀ izmaksas NO preces IEKŠĒJĀ PIEVIENOTIES Tirdzniecība ON Item.ItemID=Trade.ItemID GRUPA PĒC Preces.Nosaukums Piemērs 6.10. Summas noteikšana, par kādu tika pārdota katras preces prece.

SELECT Client.Company, Count(Deal.DealCode) AS Number_of_deals FROM Client INNER JOIN Deal ON Client.ClientId=Trade.ClientID GROUP BY Client.Company Piemērs 6.11. Katra uzņēmuma veikto darījumu skaita skaitīšana.

IZVĒLĒTIES Klients.Uzņēmums, Summa(Tirdzniecība.Daudzums) AS Kopējais_daudzums, Summa(Prece.Cena*Tirdzniecība.Daudzums) AS Izmaksas NO Preces IEKŠĒJĀ PIEVIENOTĀS (Customer INNER JOIN Trade ON Customer.CustomerId=Trade.CustomerId) ON CommoddityTraItemI. .ProductCode GROUP BY Client.Company Piemērs 6.12. Katram uzņēmumam kopējā iegādāto preču skaita un tā pašizmaksas aprēķins.

Piemērs 6.13. Nosakiet katra produkta kopējās izmaksas katram mēnesim.

ATLASĪT Preces.nosaukums, mēnesis(tirdzniecības datums) AS mēnesis, summa(prece.cena*tirdzniecība.daudzums) kā izmaksas no preces IEKŠĒJĀ PIEVIENOTIES Tirdzniecība ON Item.ItemID=Trade.ItemID GRUPA PĒC preces.Nosaukums, mēnesis(tirdzniecības datums) ) Piemērs 6.13. Katras preces kopējo izmaksu noteikšana katram mēnesim.

Piemērs 6.14. Nosakiet katras pirmās pakāpes vienības kopējās izmaksas katram mēnesim.

IZVĒLĒTIES vienumu.nosaukums, mēnesis(tirdzniecības datums) AS mēnesis, summa (prece. cena* tirdzniecība. daudzums) kā izmaksas no preces IEKŠĒJĀ PIEVIENOTIES Tirdzniecība ON Item.ItemID=Tirdzniecība.ItemID WHERE Item.Sort="Pirmā" GROUP BY Item .Nosaukums, mēnesis(darījuma datums) Piemērs 6.14. Katra pirmās šķiras produkta kopējo izmaksu noteikšana katram mēnesim.

IR piedāvājums

HAVING atspoguļo visus datu blokus, kas iepriekš grupēti pēc GROUP BY un atbilst nosacījumiem, kas norādīti sadaļā HAVING. Šī ir papildu opcija, lai "filtrētu" izvades komplektu.

Nosacījumi HAVING atšķiras no nosacījumiem WHERE:

• HAVING izslēdz no rezultātu datu kopas grupas ar apkopotu vērtību rezultātiem;
• KUR no summāro vērtību aprēķina pēc grupēšanas izslēdz ierakstus, kas neatbilst nosacījumam;
• meklēšanas nosacījumā WHERE nevar norādīt apkopotās funkcijas.

Piemērs 6.15. Nosakiet uzņēmumus, kuru kopējais darījumu skaits pārsniedz trīs.

SELECT Client.Company, Count(Trade.Count) AS Number of trades FROM Client INNER JOIN Trade ON Client.ClientCode=Trade.ClientID GROUP BY Client.Company AR SKAITS(Trade.Count)>3 Piemērs 6.15. To firmu identifikācija, kuru kopējais darījumu skaits pārsniedz trīs.

Piemērs 6.16. Parādiet to preču sarakstu, kas pārdotas par vairāk nekā 10 000 rubļu.

IZVĒLĒTIES Preces.Nosaukums, Summa (Prece.Cena*Tirdzniecība.Daudzums) KĀ izmaksas no preces IEKŠĒJĀ PIEVIENOTIES Tirdzniecība ON Prece.Item.ItemID=Tirdzniecība.PrecesID GRUPA PĒC preces.Nosaukums, kam ir summa(prece.cena*tirdzniecības daudzums)>10000. Piemērs 6.16. Tiek parādīts to preču saraksts, kas pārdotas par vairāk nekā 10 000 rubļu.

Piemērs 6.17. Parādiet to produktu sarakstu, kas pārdoti par vairāk nekā 10 000, nenorādot summu.

ATLASĪT vienumu.Nosaukums NO preces IEKŠĒJĀ PIEVIENOTIES Tirdzniecība ON Item.ItemCode=Trade.ItemID GROUP BY Item.Name HAVING Sum(Prece.Price*Trade.Quantity)>10000 Piemērs 6.17. Parādiet to produktu sarakstu, kas pārdoti par vairāk nekā 10 000, nenorādot summu.

Šajā apmācībā jūs uzzināsit, kā to izmantot SUM funkcija SQL Server (Transact-SQL) ar sintaksi un piemēriem.

Apraksts

SQL serverī (Transact-SQL) SUM funkcija atgriež izteiksmes kopējo vērtību.

Sintakse

SUM funkcijas sintakse SQL Server (Transact-SQL) ir:

VAI funkcijas SUM sintakse, grupējot rezultātus pēc vienas vai vairākām kolonnām:

Iespējas vai argumenti

izteiksme1 , izteiksme2 , ... izteiksme_n ir izteiksmes, kas nav iekļautas funkcijā SUM un ir jāiekļauj klauzulā GROUP BY SQL priekšraksta beigās.
aggregate_expression ir kolonna vai izteiksme, kas tiks summēta.
tabulas - tabulas, no kurām vēlaties iegūt ierakstus. Klauzulā FROM ir jābūt vismaz vienai tabulai.
KUR nosacījumi - pēc izvēles. Šie ir nosacījumi, kas jāievēro atlasītajiem ierakstiem.

Pieteikums

Funkciju SUM var izmantot šādās SQL Server (Transact-SQL) versijās:
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Viena lauka piemērs

Apsveriet dažus SQL piemēri Servera SUM funkcija, lai saprastu, kā izmantot SUM funkciju SQL Server (Transact-SQL).

Piemēram, varat uzzināt, kā tiek aprēķināts visu to produktu kopējais skaits, kuru skaits ir lielāks par 10.

Šajā funkcijas SUM piemērā mēs esam aizstājuši SUM(quantity) izteiksmi "Kopējais daudzums". Atgriežot rezultātu kopu - kā lauka nosaukums tiks parādīts "Kopējais daudzums".

ATŠĶIRĪGS piemērs

Funkcijā SUM varat izmantot priekšrakstu DISTINCT. Piemēram, zemāk esošais SQL priekšraksts atgriež kopējo algas summu ar unikālas vērtības algu, ja alga ir mazāka par USD 29 000 gadā.

Ja divas algas būtu 24 000 USD gadā, tikai viena no šīm vērtībām tiktu izmantota SUM funkcijā.

Formulas piemērs

SUM funkcijā ietvertajai izteiksmei nav jābūt vienam laukam. Varat arī izmantot formulu. Piemēram, jūs varat aprēķināt kopējo komisijas maksu.

Darījumi ar SQL

SELECT SUM(pārdošana * 0.03) AS "Kopā komisija" NO pasūtījumiem;

SELECT SUM (pārdošana * 0,03 ) AS "Kopējā komisija" NO pasūtījumiem ;

GROUP BY piemērā

Dažos gadījumos jums būs jāizmanto klauzula GROUP BY ar funkciju SUM.

SQL - 11. nodarbība. Kopsavilkuma funkcijas, aprēķinātās kolonnas un skati

Galīgās funkcijas sauc arī par statistiskām, apkopojošām vai summējošām. Šīs funkcijas apstrādā rindu kopu, lai saskaitītu un atgrieztu vienu vērtību. Ir piecas šādas funkcijas:

• AVG() Funkcija atgriež kolonnas vidējo vērtību.


• COUNT() Funkcija atgriež kolonnas rindu skaitu.


• MAX() Funkcija atgriež kolonnas lielāko vērtību.


• MIN() Funkcija atgriež mazāko vērtību kolonnā.


• SUM() Funkcija atgriež kolonnas vērtību summu.

Vienu no tiem - COUNT() - mēs jau tikāmies 8. nodarbībā. Tagad iepazīsimies ar pārējiem. Pieņemsim, ka mēs vēlējāmies uzzināt mūsu veikalā esošo grāmatu minimālo, maksimālo un vidējo cenu. Pēc tam no tabulas Cenas (cenas) ir jāņem minimālās, maksimālās un vidējās vērtības cenu kolonnai. Pieprasījums ir vienkāršs:

SELECT MIN(cena), MAX(cena), AVG(cena) NO cenām;

Tagad mēs vēlamies noskaidrot, par cik piegādātājs "Tigrāfija" (id=2) mums atveda preces. Izteikt šādu pieprasījumu nav viegli. Padomāsim, kā to pagatavot:

1. Vispirms no tabulas Piegādes (ienākošās) ir jāizvēlas identifikatori (id_incoming) tām piegādēm, kuras veica "Drukas" piegādātājs (id=2):

2. Tagad no Piegādes žurnāla tabulas (žurnāls_ienākošais) jāizvēlas preces (id_product) un to daudzums (daudzums), kas tika veiktas 1. punktā atrodamajās piegādēs. Tas nozīmē, ka pieprasījums no 1. punkta kļūst ligzdots:

3. Tagad iegūtajai tabulai jāpievieno cenas par atrastajām precēm, kas tiek saglabātas tabulā Cenas. Tas nozīmē, ka mums ir jāapvieno tabulas Piegādes žurnāls (žurnāls_ienākošais) un Cenas (cenas) ar kolonnu id_product:

4. Iegūtajā tabulā skaidri trūkst kolonnas Summa, tas ir aprēķinātā kolonna. Iespēja izveidot šādas kolonnas tiek nodrošināta MySQL. Lai to izdarītu, vaicājumā vienkārši jānorāda aprēķinātās kolonnas nosaukums un tas, kas tai jāaprēķina. Mūsu piemērā šāda kolonna tiks saukta par summa, un tā aprēķinās daudzuma un cenas kolonnu reizinājumu. Jaunās kolonnas nosaukums ir atdalīts ar vārdu AS:

SELECT žurnāla_ienākošais.id_produkts, žurnāla_ienākošais.daudzums, cenas.cena, žurnāls_ienākošais.daudzums*cenas.cena AS summa FROM žurnāls_ienākums, cenas WHERE žurnāls_ienākošais.id_produkts= cenas.id_produkts UN id_ienākums= (SELECT id_incoming FROM incoming WHERE2);

5. Lieliski, mums atliek vien apkopot summas aili un beidzot noskaidrot, par cik piegādātājs "Tigrāfija" mums atveda preces. Funkcijas SUM() izmantošanas sintakse ir šāda:

SELECT SUM(kolonnas_nosaukums) NO tabulas_nosaukums;

Mēs zinām kolonnas nosaukumu - summa, bet mums nav tabulas nosaukuma, jo tas ir vaicājuma rezultāts. Ko darīt? Šādiem gadījumiem MySQL ir skati. Skats ir atlases vaicājums, kam tiek piešķirts unikāls nosaukums un ko var saglabāt datu bāzē vēlākai lietošanai.

Skata izveides sintakse ir šāda:

CREATE VIEW view_name AS vaicājums;

Saglabāsim pieprasījumu kā skatu ar nosaukumu report_vendor:

CREATE VIEW report_vendor AS IZVĒLĒTIES žurnāls_ienākošais.id_produkts, žurnāla_ienākošais.daudzums, cenas.cena, žurnāla_ienākošais.daudzums*cenas.cena AS summa FROM žurnāls_ienākums, cenas KUR žurnāls_ienākošais.id_produkts= cenas.id_produkts UN id_ienākums= (SELECT FROM2 id_incomingdoringH );

6. Tagad varat izmantot galīgo SUM() funkciju:

SELECT SUM(summa) NO report_vendor;

Šeit mēs esam sasnieguši rezultātu, lai gan šim nolūkam bija jāizmanto ligzdoti vaicājumi, savienojumi, aprēķinātās kolonnas un skati. Jā, dažreiz ir jādomā, lai iegūtu rezultātu, bez tā jūs nevarat tikt nekur. Bet mēs pieskārāmies divām ļoti svarīgām tēmām – aprēķinātajām kolonnām un skatījumiem. Parunāsim par tiem sīkāk.

Aprēķinātie lauki (kolonnas)

Piemēram, mēs šodien esam apsvēruši matemātiski aprēķināto lauku. Šeit vēlos piebilst, ka var izmantot ne tikai reizināšanas operāciju (*), bet arī atņemšanu (-), saskaitīšanu (+) un dalīšanu (/). Sintakse ir šāda:

SELECT kolonnas_nosaukums_1, kolonnas_nosaukums_2, kolonnas_nosaukums_1*kolas_nosaukums_2 AS aprēķinātais_kolonnas_nosaukums NO tabulas_nosaukums;

Otra nianse ir AS atslēgvārds, mēs to izmantojām, lai iestatītu aprēķinātās kolonnas nosaukumu. Faktiski šis atslēgvārds iestata aizstājvārdus visām kolonnām. Kāpēc tas ir vajadzīgs? Koda samazināšanai un lasāmībai. Piemēram, mūsu skats varētu izskatīties šādi:

CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, cenas AS B WHERE A.id_product= B.id_product AND id_incoming= (ATLASES id_incoming FROM incoming WHERE id_vendor=2);

Piekrītiet, ka tas ir daudz īsāks un skaidrāks.

Pārstāvība

Mēs jau esam apsvēruši skatu izveides sintaksi. Kad skati ir izveidoti, tos var izmantot tāpat kā tabulas. Tas ir, veiciet tiem vaicājumus, filtrējiet un kārtojiet datus, apvienojiet vienu skatu ar citu. No vienas puses, tas ir ļoti ērts veids, kā uzglabāt bieži lietotus sarežģīti vaicājumi(kā mūsu piemērā).

Bet jāatceras, ka skati nav tabulas, tas ir, tie neuzglabā datus, bet tikai izgūst tos no citām tabulām. Līdz ar to, pirmkārt, mainot datus tabulās, mainīsies arī prezentācijas rezultāti. Un, otrkārt, kad tiek pieprasīts skats, tiek meklēti nepieciešamie dati, tas ir, DBVS veiktspēja samazinās. Tāpēc tos nedrīkst ļaunprātīgi izmantot.

Kā es varu uzzināt konkrēta pārdevēja ražoto datoru modeļu skaitu? Kā noteikt vidējo cenu datoriem, kuriem ir tāds pats specifikācijas? Uz šiem un daudziem citiem jautājumiem, kas saistīti ar kādu statistikas informāciju, var atbildēt ar palīdzību kopsavilkuma (apkopošanas) funkcijas. Standarts nodrošina šādas apkopotas funkcijas:

Visas šīs funkcijas atgriež vienu vērtību. Tajā pašā laikā funkcijas COUNT, MIN Un MAX attiecas uz jebkuru datu tipu, kamēr SUMMA Un AVG izmanto tikai ciparu laukiem. Atšķirība starp funkciju SKAITĪT(*) Un COUNT(<имя поля>) ir tas, ka otrajā, aprēķinot, netiek ņemtas vērā NULL vērtības.

Piemērs. Atrodiet minimālo un maksimālo cenu personālajiem datoriem:

Piemērs. Atrodiet pieejamo ražotāja A ražoto datoru skaitu:

Piemērs. Ja mūs interesē ražotāja A ražoto dažādu modeļu skaits, tad vaicājumu var formulēt šādi (izmantojot to, ka katrs modelis Preču tabulā tiek ierakstīts vienu reizi):

Piemērs. Atrodiet pieejamo dažādu ražotāju A ražoto modeļu skaitu. Vaicājums ir līdzīgs iepriekšējam, kurā bija jānosaka kopējais ražotāja A ražoto modeļu skaits. Šeit ir jāatrod dažādu modeļu skaits datora tabulā (t.i., pieejams pārdošanai).

Lai nodrošinātu, ka statistikas rādītāju iegūšanai tiek izmantotas tikai unikālas vērtības, kad apkopotās funkcijas arguments Var izmantot DISTINCT parametrs. Cits parametrs VISI ir noklusējuma vērtība, un tiek sagaidāms, ka kolonnā tiks uzskaitītas visas atgriešanas vērtības. operators,

Ja mums ir nepieciešams iegūt datoru modeļu skaitu, ko ražo katrs ražotājs, jums būs jāizmanto GROUP PĒC piedāvājuma, sintaktiski seko pēc KUR klauzulas.

GROUP PĒC piedāvājuma

GROUP PĒC piedāvājuma tiek izmantots, lai definētu izvadlīniju grupas, kurām var lietot apkopotās funkcijas (COUNT, MIN, MAX, AVG un SUM). Ja šīs klauzulas trūkst un tiek izmantotas apkopotās funkcijas, tad visas kolonnas ar nosaukumiem, kas minēti ATLASĪT, jāiekļauj agregētās funkcijas, un šīs funkcijas attieksies uz visu rindu kopu, kas apmierina vaicājuma predikātu. Pretējā gadījumā visas saraksta SELECT kolonnas, nav iekļauts apkopotās funkcijās, ir jānorāda V piedāvājums GROUP BY. Rezultātā visas vaicājuma izvades rindas tiek sadalītas grupās, kurām šajās kolonnās ir raksturīgas vienādas vērtību kombinācijas. Pēc tam katrai grupai tiks piemērotas apkopotās funkcijas. Ņemiet vērā, ka GROUP BY visas NULL vērtības tiek uzskatītas par vienādām, t.i. grupējot pēc lauka, kurā ir NULL vērtības, visas šādas rindas tiks iekļautas vienā grupā.
Ja ja ir klauzula GROUP BY, klauzulā SELECT nav apkopotas funkcijas, tad vaicājums vienkārši atgriezīs vienu rindu no katras grupas. Šo līdzekli kopā ar DISTINCT atslēgvārdu var izmantot, lai rezultātu kopā novērstu dublētās rindas.
Apsveriet vienkāršu piemēru:

ATLASĪT modeli, COUNT(modelis) AS Daudzums_modelis, AVG(cena) AS Vidējā_cena NO PC GROUP BY modeļa;

Šajā vaicājumā katram datora modelim tiek noteikts to skaits un vidējās izmaksas. Visas rindas ar vienādu modeļa vērtību (modeļa numuru) veido grupu, un SELECT izvade aprēķina vērtību skaitu un vidējās cenas vērtības katrai grupai. Vaicājuma rezultāts būs šāda tabula:

modelis	Daudzuma_modelis	Vidējā_cena
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Ja SELECT būtu kolonna ar datumu, tad šos rādītājus varētu aprēķināt katram konkrētajam datumam. Lai to izdarītu, jums jāpievieno datums kā grupēšanas kolonna, un pēc tam katrai vērtību kombinācijai (modeļa datums) tiks aprēķinātas apkopotās funkcijas.

Ir vairāki specifiski summāro funkciju izpildes noteikumi:

• Ja pieprasījuma rezultātā nav saņemta neviena rinda(vai vairāk nekā viena rinda šai grupai), tad nav sākotnējo datu nevienas apkopotās funkcijas aprēķināšanai. Šajā gadījumā COUNT funkciju izpildes rezultāts būs nulle, bet visu pārējo funkciju rezultāts būs NULL.
• Arguments agregāta funkcija nevar saturēt apkopotas funkcijas(funkcija no funkcijas). Tie. vienā pieprasījumā nav iespējams, teiksim, saņemt maksimālo vidējo vērtību.
• Funkcijas COUNT izpildes rezultāts ir vesels skaitlis(VESELS SKAITLIS). Citas apkopotās funkcijas manto apstrādāto vērtību datu tipus.
• Ja, izpildot funkciju SUM, tika iegūts rezultāts, kas pārsniedz izmantotā datu tipa maksimālo vērtību, kļūda.

Tātad, ja pieprasījums nesatur GROUP BY piedāvājumi, Tas agregētās funkcijas iekļauts SELECT klauzula, tiek izpildītas visās iegūtajās vaicājuma virknēs. Ja pieprasījums satur GROUP PĒC piedāvājuma, katra rindu kopa, kurai ir vienādas vērtības kolonnai vai kolonnu grupai, kas norādīta piedāvājums GROUP BY, veido grupu, un agregētās funkcijas veic katrai grupai atsevišķi.

IR piedāvājums

Ja KUR klauzula definē predikātu virknes filtrēšanai, tad IR klauzula piemērots pēc grupēšanas lai definētu līdzīgas predikātu filtrēšanas grupas pēc vērtībām summētās funkcijas. Šī klauzula ir nepieciešama, lai apstiprinātu vērtības, kas iegūtas ar agregāta funkcija nevis no atsevišķām ieraksta avota rindām, kas definētas NO klauzulas, un no šādu līniju grupas. Tāpēc šādu čeku nevar ietvert KUR klauzula.