Låt oss lära oss att sammanfatta. Nej, det här är inte resultaten av att studera SQL, utan resultaten av värdena i kolumnerna i databastabellerna. SQL-aggregatfunktioner arbetar på värdena i en kolumn för att producera ett enda resulterande värde. De vanligaste SQL-aggregatfunktionerna är SUM, MIN, MAX, AVG och COUNT. Det är nödvändigt att skilja mellan två fall av användning av aggregerade funktioner. Först används aggregerade funktioner på egen hand och returnerar ett enda resulterande värde. För det andra används aggregerade funktioner med SQL GROUP BY-satsen, det vill säga gruppering efter fält (kolumner) för att erhålla de resulterande värdena i varje grupp. Låt oss först överväga fall av användning av aggregerade funktioner utan gruppering.

SQL SUM-funktion

SQL SUM-funktionen returnerar summan av värdena i en databastabellkolumn. Det kan endast tillämpas på kolumner vars värden är siffror. SQL-frågor för att få den resulterande summan att börja så här:

VÄLJ SUMMA (COLUMN_NAME) ...

Detta uttryck följs av FROM (TABLE_NAME), och sedan kan ett villkor anges med hjälp av WHERE-satsen. Dessutom kan kolumnnamnet föregås av DISTINCT, vilket betyder att endast unika värden kommer att räknas. Som standard tas alla värden i beaktande (för detta kan du specifikt ange inte DISTINCT, utan ALLA, men ordet ALLA krävs inte).

Om du vill köra databasfrågor från den här lektionen på MS SQL Server, men detta DBMS inte är installerat på din dator, kan du installera det med instruktionerna på denna länk .

Först kommer vi att arbeta med företagsdatabasen - Företag1. Skriptet för att skapa denna databas, dess tabeller och fylla tabellerna med data finns i filen på denna länk .

Exempel 1. Det finns en företagsdatabas med uppgifter om dess divisioner och anställda. Förutom allt annat har tabellen Personal en kolumn med uppgifter om anställdas löner. Urvalet från tabellen ser ut så här (för att förstora bilden, klicka på den med vänster musknapp):

För att få summan av alla löner använder vi följande fråga (på MS SQL Server - med föregående konstruktion USE company1;):

VÄLJ SUMMA (Lön) FRÅN Personal

Denna fråga returnerar värdet 287664.63.

Och nu . I övningarna börjar vi redan komplicera uppgifterna och föra dem närmare de som vi stöter på i praktiken.

SQL MIN funktion

SQL MIN-funktionen fungerar också på kolumner vars värden är siffror och returnerar minimum av alla värden i kolumnen. Denna funktion har en syntax som liknar den för SUM-funktionen.

Exempel 3. Databasen och tabellen är desamma som i exempel 1.

Vi måste ta reda på minimilönen för anställda på avdelning nummer 42. För att göra detta, skriv följande fråga (på MS SQL Server - med prefixet USE company1;):

Frågan returnerar värdet 10505.90.

Och igen träna för oberoende beslut . I den här och några andra övningar behöver du inte bara personaltabellen utan också orgtabellen, som innehåller data om företagets divisioner:

Exempel 4. Organisationstabellen läggs till i tabellen Personal, som innehåller data om företagets avdelningar. Skriv ut det minsta antalet år som en anställd arbetat på en avdelning i Boston.

SQL MAX-funktion

SQL MAX-funktionen fungerar på liknande sätt och har en liknande syntax, som används när du behöver bestämma maxvärdet bland alla värden i en kolumn.

Exempel 5.

Vi måste ta reda på den maximala lönen för anställda i avdelning nummer 42. För att göra detta, skriv följande fråga (på MS SQL Server - med prefixet USE company1;):

Frågan returnerar värdet 18352.80

Det är dags övningar för oberoende lösning.

Exempel 6. Vi arbetar återigen med två bord - Personal och Org. Visa namnet på avdelningen och det maximala värdet av den provision som en anställd har fått på avdelningen som tillhör gruppen av avdelningar (Division) Östra. Använda sig av JOIN (sammanfoga tabeller) .

SQL AVG-funktion

Det som står angående syntaxen för de tidigare beskrivna funktionerna gäller även för SQL AVG-funktionen. Denna funktion returnerar medelvärdet av alla värden i en kolumn.

Exempel 7. Databasen och tabellen är desamma som i de tidigare exemplen.

Låt oss säga att du vill ta reda på den genomsnittliga tjänstgöringstiden för anställda i avdelning nummer 42. För att göra detta, skriv följande fråga (på MS SQL Server - med den föregående konstruktionen USE company1;):

Resultatet blir 6,33

Exempel 8. Vi arbetar med ett bord - Personal. Visa medellönen för anställda med 4 till 6 års erfarenhet.

SQL COUNT-funktion

SQL COUNT-funktionen returnerar antalet poster i en databastabell. Om du anger SELECT COUNT(COLUMN_NAME) ... i frågan blir resultatet antalet poster utan att ta hänsyn till de poster där kolumnvärdet är NULL (odefinierat). Om du använder en asterisk som argument och börja VÄLJ fråga COUNT(*) ..., då blir resultatet antalet av alla poster (rader) i tabellen.

Exempel 9. Databasen och tabellen är desamma som i de tidigare exemplen.

Du vill veta antalet anställda som får provision. Antalet anställda vars Comm-kolumnvärden inte är NULL kommer att returneras av följande fråga (på MS SQL Server - med prefixet USE company1;):

VÄLJ ANTAL (Komm) FRÅN Personal

Resultatet blir 11.

Exempel 10. Databasen och tabellen är desamma som i de tidigare exemplen.

Om du vill ta reda på det totala antalet poster i tabellen, använd sedan en fråga med en asterisk som argument till COUNT-funktionen (på MS SQL Server - med föregående konstruktion USE company1;):

VÄLJ ANTAL (*) FRÅN Personal

Resultatet blir 17.

I nästa övning för oberoende lösning du måste använda en underfråga.

Exempel 11. Vi arbetar med ett bord - Personal. Visa antalet anställda på planeringsavdelningen (Plains).

Aggregera funktioner med SQL GROUP BY

Låt oss nu titta på hur vi använder aggregerade funktioner tillsammans med SQL GROUP BY-satsen. SQL GROUP BY-satsen används för att gruppera resultatvärden efter kolumner i en databastabell. Webbplatsen har en lektion tillägnad separat till denna operatör .

Vi kommer att arbeta med databasen "Annonsportal 1". Skriptet för att skapa denna databas, dess tabell och fylla i datatabellen finns i filen på denna länk .

Exempel 12. Så det finns en databas för annonsportalen. Den har en annonstabell som innehåller information om annonser som skickats in för veckan. Kolumnen Kategori innehåller data om stora annonskategorier (till exempel Fastigheter), och Kolumnen Delar innehåller data om mindre delar som ingår i kategorierna (till exempel är delarna Lägenheter och Sommarhus delar av kategorin Fastigheter). Kolumnen Enheter innehåller uppgifter om antalet inskickade annonser och kolumnen Pengar innehåller uppgifter om hur mycket pengar som tagits emot för att skicka in annonser.

Kategori	Del	Enheter	Pengar
Transport	Bilar	110	17600
Fastighet	Lägenheter	89	18690
Fastighet	Dachas	57	11970
Transport	Motorcyklar	131	20960
Byggmaterial	Brädor	68	7140
Elektroteknik	TV-apparater	127	8255
Elektroteknik	Kylskåp	137	8905
Byggmaterial	Regips	112	11760
Fritid	Böcker	96	6240
Fastighet	Hemma	47	9870
Fritid	musik	117	7605
Fritid	Spel	41	2665

Använd SQL GROUP BY-satsen för att hitta hur mycket pengar som tjänats in på att publicera annonser i varje kategori. Vi skriver följande fråga (på MS SQL Server - med föregående konstruktion USE adportal1;):

VÄLJ Kategori, SUMMA (Pengar) SOM pengar FRÅN ANNONSER GRUPPER EFTER Kategori

Exempel 13. Databasen och tabellen är desamma som i föregående exempel.

Ta reda på vilken del av varje kategori som hade flest listor med hjälp av SQL GROUP BY-satsen. Vi skriver följande fråga (på MS SQL Server - med föregående konstruktion USE adportal1;):

VÄLJ Kategori, Del, MAX (Enheter) AS Maximum FRÅN ANNONSGRUPP EFTER Kategori

Resultatet blir följande tabell:

Totala och individuella värden kan erhållas i en tabell kombinera frågeresultat med UNION-operatorn .

Relationsdatabaser data och SQL-språk

Beskriver användningen av aritmetiska operatorer och konstruktionen av beräknade kolumner. De sista (sammanlagda) funktionerna COUNT, SUM, AVG, MAX, MIN beaktas. Ger ett exempel på hur operatorn GROUP BY används för gruppering i dataurvalsfrågor. Beskriver användningen av HAVING-satsen.

Bygger beräknade fält

I allmänhet att skapa beräknat (härlett) fält i SELECT-listan bör du ange något uttryck SQL-språk. Dessa uttryck använder aritmetiska operationer addition, subtraktion, multiplikation och division, samt inbyggda funktioner i SQL-språket. Du kan ange namnet på valfri kolumn (fält) i en tabell eller fråga, men använd bara kolumnnamnet på tabellen eller frågan som är listad i FROM-satslistan för motsvarande sats. När man konstruerar komplexa uttryck kan parenteser behövas.

SQL-standarder tillåter dig att uttryckligen ange namnen på kolumnerna i den resulterande tabellen, för vilka AS-satsen används.

VÄLJ Product.Name, Product.Price, Deal.Quantity, Product.Price*Deal.Quantity SOM kostnad FRÅN Produkt INNER JOIN Deal PÅ Product.ProductCode=Deal.ProductCode Exempel 6.1. Beräkning av den totala kostnaden för varje transaktion.

Exempel 6.2. Få en lista över företag som anger kundernas efternamn och initialer.

VÄLJ Företag, Efternamn+""+ Vänster(Förnamn,1)+"."+Vänster(Mellannamn,1)+"."SOM Fullständigt Namn FRÅN klient Exempel 6.2. Få en lista över företag som anger efternamn och initialer på kunder.

Begäran använder den inbyggda vänsterfunktionen, som låter dig klippa ett tecken från vänster i en textvariabel i det här fallet.

Exempel 6.3. Få en lista över produkter som anger försäljningsår och månad.

VÄLJ Product.Name, Year(Transaction.Date) AS Year, Month(Transaction.Date) AS Month FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode Exempel 6.3. Får en lista över produkter som anger försäljningsår och månad.

Frågan använder de inbyggda funktionerna Year och Month för att extrahera år och månad från ett datum.

Använda sammanfattningsfunktioner

Genom att använda slutliga (sammanslagna) funktioner inom SQL-frågan kan du få ett antal allmän statistisk information om uppsättningen av utvalda värden för utdatauppsättningen.

Användaren har tillgång till följande grundläggande sammanfattningsfunktioner:

• Count (Expression) - bestämmer antalet poster i SQL-frågans utdatauppsättning;
• Min/Max (uttryck) - bestäm den minsta och största av uppsättningen värden i ett visst förfrågningsfält;
• Avg (Uttryck) - den här funktionen låter dig beräkna medelvärdet av en uppsättning värden lagrade i ett specifikt fält med poster som valts av en fråga. Det är ett aritmetiskt medelvärde, d.v.s. summan av värden dividerat med deras antal.
• Summa (uttryck) - Beräknar summan av uppsättningen värden som finns i ett specifikt fält av posterna som valts av frågan.

Oftast används kolumnnamn som uttryck. Uttrycket kan också beräknas med hjälp av värden i flera tabeller.

Alla dessa funktioner fungerar på värden i en enda tabellkolumn eller aritmetiskt uttryck och returnerar ett enda värde. Funktionerna COUNT , MIN och MAX gäller för både numeriska och icke-numeriska fält, medan funktionerna SUM och AVG endast kan användas för numeriska fält, med undantag för COUNT(*) . Vid beräkning av resultaten av en funktion elimineras först alla nollvärden, och sedan tillämpas den nödvändiga operationen endast på de återstående specifika kolumnvärdena. Alternativet COUNT(*) är ett speciellt användningsfall av COUNT-funktionen. dess syfte är att räkna alla rader i den resulterande tabellen, oavsett om den innehåller nollvärden, dubbletter eller andra värden.

Om du behöver eliminera dubbletter av värden innan du använder en generisk funktion, måste du föregå kolumnnamnet i funktionsdefinitionen med nyckelordet DISTINCT. Det har ingen betydelse för MIN- och MAX-funktionerna, men dess användning kan påverka resultaten av SUM- och AVG-funktionerna, så du måste överväga om den ska finnas i varje enskilt fall. Dessutom kan nyckelordet DISTINCT endast anges en gång i en fråga.

Det är mycket viktigt att notera det sammanfattningsfunktioner kan endast användas i en lista i en SELECT-sats och som en del av en HAVING-sats. I alla andra fall är detta oacceptabelt. Om listan i SELECT-satsen innehåller sammanfattningsfunktioner, och frågetexten inte innehåller en GROUP BY-sats, som gör det möjligt att kombinera data till grupper, kan inget av listelementen i SELECT-satsen innehålla några referenser till fält, förutom i situationen där fälten fungerar som argument slutliga funktioner.

Exempel 6.4. Bestäm det första alfabetiska namnet på produkten.

VÄLJ Min(Product.Name) AS Min_Name FRÅN Produkt Exempel 6.4. Bestämning av produktens första alfabetiska namn.

Exempel 6.5. Bestäm antalet transaktioner.

SELECT Count(*) AS Number_of_deals FROM Deal Exempel 6.5. Bestäm antalet transaktioner.

Exempel 6.6. Bestäm den totala mängden sålda varor.

SELECT Sum(Deal.Quantity) AS Item_Quantity FROM Deal Exempel 6.6. Fastställande av den totala mängden sålda varor.

Exempel 6.7. Bestäm det genomsnittliga priset på sålda varor.

SELECT Avg(Product.Price) AS Avg_Price FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode; Exempel 6.7. Fastställande av det genomsnittliga priset på sålda varor.

VÄLJ Summa (Produkt. Pris* Transaktion. Kvantitet) SOM kostnad FRÅN Produkt INNER JOIN Transaktion PÅ Product.ProductCode=Transaction.ProductCode Exempel 6.8. Beräknar den totala kostnaden för sålda varor.

GROUP BY-klausul

Frågor kräver ofta generering av delsummor, vilket vanligtvis indikeras av utseendet på frasen "för varje..." i frågan. För detta ändamål i SELECT-sats GROUP BY-satsen används. En fråga som innehåller GROUP BY kallas en grupperingsfråga eftersom den grupperar data som returneras av SELECT-operationen och sedan skapar en enda sammanfattningsrad för varje enskild grupp. SQL-standarden kräver att SELECT-satsen och GROUP BY-satsen är nära relaterade. När en SELECT-sats innehåller en GROUP BY-sats måste varje listelement i SELECT-satsen ha ett enda värde för hela gruppen. Dessutom kan SELECT-satsen endast inkludera följande typer av element: fältnamn, sammanfattningsfunktioner, konstanter och uttryck som inkluderar kombinationer av elementen som anges ovan.

Alla fältnamn som anges i SELECT-satsen måste också finnas i GROUP BY-satsen - om inte kolumnnamnet används i slutlig funktion. Den omvända regeln är inte sann - GROUP BY-satsen kan innehålla kolumnnamn som inte finns i listan över SELECT-satsen.

Om en WHERE-sats används tillsammans med GROUP BY, bearbetas den först, och endast de rader som uppfyller sökvillkoret grupperas.

SQL-standarden anger att vid gruppering behandlas alla saknade värden som lika. Om två tabellrader i samma grupperingskolumn innehåller ett NULL-värde och identiska värden i alla andra icke-null-grupperingskolumner, placeras de i samma grupp.

Exempel 6.9. Beräkna den genomsnittliga volymen av inköp som gjorts av varje kund.

VÄLJ Client.LastName, Avg(Transaction.Quantity) AS Average_Quantity FROM Client INNER JOIN Handel PÅ Client.ClientCode=Transaction.ClientCode GRUPPER EFTER Client.LastName Exempel 6.9. Beräkna den genomsnittliga volymen av inköp som gjorts av varje kund.

Frasen "varje köpare" återspeglas i SQL-frågan i form av en mening GROUP BY Client.LastName.

Exempel 6.10. Bestäm hur mycket varje produkt såldes för.

VÄLJ Product.Name, Sum(Product.Price*Transaction.Quantity) SOM kostnad FRÅN Product INNER JOIN Deal PÅ Product.ProductCode=Transaction.ProductCode GRUPPER EFTER Product.Name Exempel 6.10. Fastställande av det belopp som varje produkt såldes för.

VÄLJ Client.Company, Count(Transaction.TransactionCode) AS Antal_transaktioner FRÅN Client INNER JOIN Transaktion PÅ Client.ClientCode=Transaction.ClientCode GRUPPER AV Client.Company Exempel 6.11. Räknar antalet transaktioner som genomförts av varje företag.

VÄLJ 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=Transaction .Produktkod GRUPPER AV Client.Company Exempel 6.12. Beräkning av den totala mängden inköpta varor för varje företag och dess kostnad.

Exempel 6.13. Bestäm den totala kostnaden för varje produkt för varje månad.

VÄLJ 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) ) Exempel 6.13. Fastställande av den totala kostnaden för varje produkt för varje månad.

Exempel 6.14. Bestäm den totala kostnaden för varje förstklassig produkt för varje månad.

VÄLJ Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Kostnad FRÅN Produkt INNER JOIN Transaktion PÅ Product.ProductCode=Transaction.ProductCode WHERE Product.Grade="First" GROUP BY Product .Name, Month(Transaction.Date) Exempel 6.14. Fastställande av den totala kostnaden för varje förstklassig produkt för varje månad.

HA erbjudande

Med HAVING återspeglas alla datablock som tidigare grupperats med GROUP BY som uppfyller villkoren specificerade i HAVING. Detta ytterligare möjlighet"filtrera" utgångsuppsättningen.

Villkoren i HAVING skiljer sig från villkoren i WHERE:

• HAVING utesluter grupper med aggregerade värderesultat från den resulterande datamängden;
• WHERE utesluter poster som inte uppfyller villkoret från beräkningen av aggregerade värden genom gruppering;
• Aggregatfunktioner kan inte anges i sökvillkoret WHERE.

Exempel 6.15. Identifiera företag vars totala antal transaktioner översteg tre.

VÄLJ Client.Company, Count(Trade.Quantity) AS Number_of_deals FROM Client INNER JOIN Handel PÅ Client.ClientCode=Transaktion.ClientCode GRUPPER AV Client.Company HAR Count(Transaction.Quantity)>3 Exempel 6.15. Identifiering av företag vars totala antal transaktioner översteg tre.

Exempel 6.16. Visa en lista över varor som säljs för mer än 10 000 rubel.

VÄLJ Product.Name, Sum(Produkt.Pris*Deal.Quantity) SOM kostnad FRÅN Product INNER JOIN Deal PÅ Product.ProductCode=Transaktion.ProductCode GRUPPER EFTER Produkt.Namn ATT HAR Summa(Produkt.Pris*Deal.Quantity)>10000 Exempel 6.16. Visar en lista över varor som säljs för mer än 10 000 rubel.

Exempel 6.17. Visa en lista över produkter som sålts för mer än 10 000 utan att ange beloppet.

VÄLJ Product.Name FRÅN Product INNER JOIN Deal PÅ Product.ProductCode=Deal.ProductCode GRUPPER EFTER Produkt.Namn ATT HAR Summa(Produkt.Pris*Transaktion.Quantitet)>10000 Exempel 6.17. Visa en lista över produkter som sålts för mer än 10 000 utan att ange beloppet.

I den här handledningen kommer du att lära dig hur du använder SUM-funktion i SQL Server (Transact-SQL) med syntax och exempel.

Beskrivning

I SQL Server (Transact-SQL) SUM-funktion returnerar det totala värdet av ett uttryck.

Syntax

Syntaxen för SUM-funktionen i SQL Server (Transact-SQL) är:

ELLER syntaxen för SUM-funktionen när resultat grupperas efter en eller flera kolumner är:

Parametrar eller argument

expression1 , expression2 , ... expression_n är uttryck som inte ingår i SUM-funktionen och måste inkluderas i GROUP BY-satsdelen i slutet av SQL-satsen.
aggregate_expression är kolumnen eller uttrycket som kommer att aggregeras.
tabeller - tabeller som du vill hämta poster från. Det måste finnas minst en tabell i FROM-satsen.
WHERE villkor - valfritt. Dessa är villkoren som måste uppfyllas för de valda posterna.

Ansökan

SUM-funktionen kan användas i följande versioner av SQL Server (Transact-SQL):
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

Exempel med ett fält

Låt oss titta på några exempel på SQL Server SUM-funktioner för att förstå hur man använder SUM-funktionen i SQL Server (Transact-SQL).

Du kan till exempel ta reda på den totala kvantiteten för alla produkter vars kvantitet är större än 10.

I det här exemplet på SUM-funktionen har vi kallat uttrycket SUM(quantity) "Total Quantity". När du returnerar en resultatuppsättning - "Total kvantitet" visas som fältnamnet.

Exempel på användning av DISTINCT

Du kan använda operatorn DISTINCT i SUM-funktionen. Till exempel returnerar följande SQL-sats den totala lönen med unika värden lön, där lönen är under 29 000 USD per år.

Om de två lönerna var $24 000 per år, skulle bara ett av dessa värden användas i SUM-funktionen.

Exempel på användning av formeln

Uttrycket i SUM-funktionen behöver inte vara ett enda fält. Du kan också använda formeln. Du kan till exempel beräkna den totala provisionen.

Transact-SQL

VÄLJ SUMMA(försäljning * 0,03) SOM "Total provision" FRÅN beställningar;

VÄLJ SUMMA (försäljning * 0,03 ) SOM "Total provision" FRÅN beställningar;

Exempel på användning av GROUP BY

I vissa fall måste du använda operatorn GROUP BY med SUM-funktionen.

SQL - Lektion 11. Totalt antal funktioner, beräknade kolumner och vyer

Totalfunktioner kallas också statistiska, aggregerade eller summafunktioner. Dessa funktioner bearbetar en uppsättning strängar för att räkna och returnera ett enda värde. Det finns bara fem sådana funktioner:

• Funktionen AVG() returnerar medelvärdet för en kolumn.


• COUNT() Funktionen returnerar antalet rader i en kolumn.


• Funktionen MAX() returnerar det största värdet i en kolumn.


• Funktionen MIN() returnerar det minsta värdet i kolumnen.


• SUM() Funktionen returnerar summan av kolumnvärdena.

Vi träffade redan en av dem - COUNT() - i lektion 8. Nu ska vi träffa de andra. Låt oss säga att vi ville veta lägsta, högsta och genomsnittliga priset på böcker i vår butik. Sedan från pristabellen måste du ta minimi-, max- och medelvärdena för priskolumnen. Begäran är enkel:

VÄLJ MIN(pris), MAX(pris), AVG(pris) FRÅN priser;

Nu vill vi ta reda på hur mycket varorna togs till oss av leverantören "House of Printing" (id=2). Att göra en sådan begäran är inte så lätt. Låt oss fundera på hur man komponerar det:

1. Först, från tabellen Supplies (incoming) väljer du identifierarna (id_incoming) för de leveranser som utfördes av leverantören "Print House" (id=2):

2. Från tabellen Supply Journal (magazine_incoming) måste du nu välja varorna (id_product) och deras kvantiteter (kvantitet), som utfördes i de leveranser som finns i punkt 1. Det vill säga, frågan från punkt 1 blir kapslad:

3. Nu måste vi lägga till priserna för de hittade produkterna till den resulterande tabellen, som lagras i tabellen Priser. Det vill säga, vi kommer att behöva gå med i Supply Magazine (magazine_incoming) och Prices-tabellerna med hjälp av kolumnen id_product:

4. Den resulterande tabellen saknar helt klart kolumnen Belopp, det vill säga beräknad kolumn. Möjligheten att skapa sådana kolumner finns i MySQL. För att göra detta behöver du bara ange i frågan namnet på den beräknade kolumnen och vad den ska beräkna. I vårt exempel kommer en sådan kolumn att kallas summa, och den kommer att beräkna produkten av kvantitets- och priskolumnerna. Namnet på den nya kolumnen skiljs åt med ordet AS:

VÄLJ magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming_vendor=WHERE);

5. Bra, allt vi behöver göra är att lägga samman summakolumnen och slutligen ta reda på hur mycket leverantören "House of Printing" gav oss varorna för. Syntaxen för att använda SUM()-funktionen är följande:

SELECT SUM(kolumnnamn) FRÅN tabellnamn;

Vi vet namnet på kolumnen - summa, men vi har inte namnet på tabellen, eftersom det är resultatet av en fråga. Vad ska man göra? För sådana fall har MySQL Views. En vy är en urvalsfråga som får ett unikt namn och som kan lagras i en databas för senare användning.

Syntaxen för att skapa en vy är följande:

CREATE VIEW view_name AS-begäran;

Låt oss spara vår begäran som en vy med namnet report_vendor:

CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (VÄLJ id_incoming WHERE id_incoming WHERE id_incoming );

6. Nu kan du använda den sista funktionen SUM():

VÄLJ SUMMA(summa) FRÅN rapportleverantör;

Så vi uppnådde resultatet, även om vi för detta var tvungna att använda kapslade frågor, sammanfogningar, beräknade kolumner och vyer. Ja, ibland måste man tänka för att få resultat, utan detta kommer man ingen vart. Men vi berörde två mycket viktiga ämnen - beräknade kolumner och vyer. Låt oss prata om dem mer i detalj.

Beräknade fält (kolumner)

Med ett exempel tittade vi på ett matematiskt beräknat fält idag. Här skulle jag vilja tillägga att du inte bara kan använda multiplikationsoperationen (*), utan även subtraktion (-), addition (+) och division (/). Syntaxen är som följer:

VÄLJ kolumnnamn 1, kolumnnamn 2, kolumnnamn 1 * kolumnnamn 2 AS calculated_column_name FROM tabellnamn;

Den andra nyansen är nyckelordet AS, vi använde det för att ange namnet på den beräknade kolumnen. Faktum är att det här nyckelordet används för att ställa in alias för alla kolumner. Varför är detta nödvändigt? För kodreducering och läsbarhet. Vår syn kan till exempel se ut så här:

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

Håller med om att detta är mycket kortare och tydligare.

Representation

Vi har redan tittat på syntaxen för att skapa vyer. När vyer väl har skapats kan de användas på samma sätt som tabeller. Det vill säga köra frågor mot dem, filtrera och sortera data och kombinera vissa vyer med andra. Å ena sidan är detta ett mycket bekvämt sätt att förvara ofta använda komplexa frågor(som i vårt exempel).

Men kom ihåg att vyer inte är tabeller, det vill säga att de inte lagrar data, utan bara hämtar dem från andra tabeller. För det första, när data i tabellerna ändras, kommer presentationsresultaten också att förändras. Och för det andra, när en begäran görs till en vy, genomsöks den nödvändiga informationen, det vill säga prestanda för DBMS reduceras. Därför bör du inte missbruka dem.

Hur kan jag ta reda på antalet PC-modeller som produceras av en viss leverantör? Hur man bestämmer det genomsnittliga priset på datorer som har samma specifikationer? Dessa och många andra frågor relaterade till viss statistisk information kan besvaras med hjälp av slutliga (sammanslagna) funktioner. Standarden tillhandahåller följande aggregerade funktioner:

Alla dessa funktioner returnerar ett enda värde. Samtidigt funktionerna COUNT, MIN Och MAX gäller för alla datatyper, medan BELOPP Och AVG används endast för numeriska fält. Skillnad mellan funktioner RÄKNA(*) Och RÄKNA(<имя поля>) är att den andra inte tar hänsyn till NULL-värden vid beräkning.

Exempel. Hitta lägsta och högsta pris för persondatorer:

Exempel. Hitta det tillgängliga antalet datorer som tillverkats av tillverkare A:

Exempel. Om vi ​​är intresserade av antalet olika modeller som produceras av tillverkare A, kan frågan formuleras enligt följande (med hjälp av det faktum att i produkttabellen registreras varje modell en gång):

Exempel. Hitta antalet tillgängliga olika modeller tillverkade av tillverkare A. Frågan liknar den föregående, där det krävdes för att bestämma det totala antalet modeller som tillverkats av tillverkare A. Här behöver du också hitta antalet olika modeller i PC-bordet (dvs de som finns till försäljning).

För att vid mottagandet statistiska indikatorer Endast unika värden användes när argument för aggregerade funktioner kan användas DISTINCT parameter. Annan parameter ALLAär standard och förutsätter att alla returnerade värden i kolumnen räknas. Operatör,

Om vi ​​behöver få fram antalet PC-modeller alla tillverkare måste du använda GROUP BY-klausul, syntaktisk efter WHERE-klausuler.

GROUP BY-klausul

GROUP BY-klausul används för att definiera grupper av utdatasträngar som kan appliceras på aggregerade funktioner (COUNT, MIN, MAX, AVG och SUM). Om denna sats saknas och aggregerade funktioner används, kommer alla kolumner med namn som nämns i VÄLJ, bör ingå i samla funktioner, och dessa funktioner kommer att tillämpas på hela uppsättningen rader som uppfyller frågepredikatet. Annars alla kolumner i SELECT-listan ingår ej i aggregerade funktioner måste anges i GROUP BY-satsen. Som ett resultat är alla rader för utmatningsfråge uppdelade i grupper som kännetecknas av samma kombinationer av värden i dessa kolumner. Efter detta kommer aggregerade funktioner att tillämpas på varje grupp. Observera att för GROUP BY behandlas alla NULL-värden som lika, dvs. vid gruppering efter ett fält som innehåller NULL-värden kommer alla sådana rader att falla i en grupp.
Om om det finns en GROUP BY-sats, i SELECT-satsen inga aggregerade funktioner, då returnerar frågan helt enkelt en rad från varje grupp. Den här funktionen, tillsammans med nyckelordet DISTINCT, kan användas för att eliminera dubbletter av rader i en resultatuppsättning.
Låt oss titta på ett enkelt exempel:

VÄLJ modell, ANTAL(modell) AS Antal_modell, AVG(pris) AS Genomsnittspris FRÅN PC GROUP BY modell;

I denna begäran, för varje PC-modell, bestäms deras antal och genomsnittliga kostnad. Alla rader med samma modellvärde bildar en grupp, och utdata från SELECT beräknar antalet värden och genomsnittliga prisvärden för varje grupp. Resultatet av frågan blir följande tabell:

modell	Antal_modell	Genomsnittligt pris
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Om SELECT hade en datumkolumn skulle det vara möjligt att beräkna dessa indikatorer för varje specifikt datum. För att göra detta måste du lägga till datum som en grupperingskolumn, och sedan beräknas de aggregerade funktionerna för varje kombination av värden (modell-datum).

Det finns flera specifika regler för att utföra aggregerade funktioner:

• Om som ett resultat av begäran inga rader mottagna(eller mer än en rad för en given grupp), så finns det ingen källdata för att beräkna någon av de aggregerade funktionerna. I det här fallet blir resultatet av COUNT-funktionerna noll, och resultatet av alla andra funktioner blir NULL.
• Argument aggregerad funktion kan inte i sig innehålla aggregerade funktioner(funktion från funktion). De där. i en fråga är det omöjligt att till exempel få maximala medelvärden.
• Resultatet av att köra COUNT-funktionen är heltal(HELTAL). Andra aggregerade funktioner ärver datatyperna för de värden de bearbetar.
• Om SUM-funktionen ger ett resultat som är större än maxvärdet för den datatyp som används, fel.

Så, om begäran inte innehåller GROUP BY-satser, Den där samla funktioner ingår i SELECT-sats, exekveras på alla resulterande frågerader. Om begäran innehåller GROUP BY-klausul, varje uppsättning rader som har samma värden som en kolumn eller grupp av kolumner som anges i GROUP BY-klausul, utgör en grupp och samla funktioner utförs för varje grupp separat.

HA erbjudande

Om WHERE klausul definierar ett predikat för filtrering av rader HA erbjudande gäller efter gruppering för att definiera ett liknande predikat som filtrerar grupper efter värden aggregerade funktioner. Denna klausul behövs för att validera de värden som erhålls med hjälp av aggregerad funktion inte från enskilda rader i postkällan definierad i FRÅN klausul, och från grupper av sådana linjer. Därför kan en sådan kontroll inte ingå i WHERE klausul.