I denne opplæringen lærer du hvordan du bruker SUM funksjon i SQL Server (Transact-SQL) med syntaks og eksempler.

Beskrivelse

I SQL Server (Transact-SQL) SUM funksjon returnerer den totale verdien av et uttrykk.

Syntaks

Syntaksen for SUM-funksjonen i SQL Server (Transact-SQL) er:

ELLER syntaksen for SUM-funksjonen når resultater grupperes etter én eller flere kolonner er:

Parametere eller argumenter

expression1 , expression2 , ... expression_n er uttrykk som ikke er inkludert i SUM-funksjonen og må inkluderes i GROUP BY-leddet på slutten av SQL-setningen.
aggregate_expression er kolonnen eller uttrykket som skal samles.
tabeller - tabeller du ønsker å hente poster fra. Det må være minst én tabell oppført i FROM-klausulen.
WHERE-forhold - valgfritt. Dette er vilkårene som må oppfylles for de valgte postene.

applikasjon

SUM-funksjonen kan brukes i følgende versjoner 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

Eksempel med ett felt

La oss se på noen eksempler på SQL Server SUM-funksjoner for å forstå hvordan du bruker SUM-funksjonen i SQL Server (Transact-SQL).

Du kan for eksempel finne ut den totale mengden av alle produkter hvis antall er større enn 10.

I dette eksemplet av SUM-funksjonen har vi kalt uttrykket SUM(quantity) "Total Quantity". Når du returnerer et resultatsett - "Totalt antall" vises som feltnavn.

Eksempel på bruk av DISTINCT

Du kan bruke DISTINCT-operatoren i SUM-funksjonen. For eksempel returnerer SQL-setningen nedenfor den totale lønnen med unike lønnsverdier der lønnen er under $29 000 per år.

Hvis de to lønningene var $24 000 per år, ville bare én av disse verdiene blitt brukt i SUM-funksjonen.

Eksempel på bruk av formelen

Uttrykket i SUM-funksjonen trenger ikke å være et enkelt felt. Du kan også bruke formelen. Du kan for eksempel beregne den totale provisjonen.

Transact-SQL

VELG SUM(salg * 0,03) SOM "Total provisjon" FRA bestillinger;

VELG SUM (salg * 0,03 ) SOM "Total provisjon"

FRA bestillinger;

Eksempel på bruk av GROUP BY

I noen tilfeller må du bruke GROUP BY-operatoren med SUM-funksjonen.

SQL - Leksjon 11. Totalfunksjoner, beregnede kolonner og visninger

Totalfunksjoner kalles også statistiske, aggregerte eller sumfunksjoner. Disse funksjonene behandler et sett med strenger for å telle og returnere en enkelt verdi. Det er bare fem slike funksjoner:

AVG()-funksjonen returnerer gjennomsnittsverdien av en kolonne.

COUNT()-funksjonen returnerer antall rader i en kolonne.

MAX()-funksjonen returnerer den største verdien i en kolonne.

MIN()-funksjonen returnerer den minste verdien i kolonnen.

SUM() Funksjonen returnerer summen av kolonneverdiene.

Vi møtte allerede en av dem - COUNT() - i leksjon 8. La oss nå møte de andre. La oss si at vi ønsket å vite minimums-, maksimums- og gjennomsnittsprisen på bøker i butikken vår. Så fra pristabellen må du ta minimums-, maksimums- og gjennomsnittsverdier for priskolonnen. Forespørselen er enkel:

VELG MIN(pris), MAX(pris), AVG(pris) FRA priser;

Nå ønsker vi å finne ut hvor mye varene ble brakt til oss av leverandøren "House of Printing" (id=2). Å komme med en slik forespørsel er ikke så lett. La oss tenke på hvordan du komponerer det:

1. Først, fra rekvisitatabellen (innkommende) velger du identifikatorene (id_incoming) for de leveransene som ble utført av leverandøren "Print House" (id=2):

2. Nå fra Supply Journal-tabellen (magazine_incoming) må du velge varene (id_product) og deres mengde (quantity), som ble utført i leveransene i punkt 1. Det vil si at spørringen fra punkt 1 blir nestet:

3. Nå må vi legge til den resulterende tabellen prisene for produktene som er funnet, som er lagret i tabellen Priser. Det vil si at vi må bli med i Supply Magazine (magazine_incoming) og Prices-tabellene ved å bruke id_product-kolonnen:

4. Den resulterende tabellen mangler tydeligvis kolonnen Beløp, det vil si beregnet kolonne. Muligheten til å lage slike kolonner er gitt i MySQL. For å gjøre dette trenger du bare å angi navnet på den beregnede kolonnen i spørringen og hva den skal beregne. I vårt eksempel vil en slik kolonne hete summa, og den vil beregne produktet av mengde- og priskolonnene. Navnet på den nye kolonnen er atskilt med ordet 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= (SELECT id_incoming FROM incoming_vendor=HER2);

5. Flott, alt vi trenger å gjøre er å legge sammen summakolonnen og til slutt finne ut hvor mye leverandøren "House of Printing" brakte oss varene for. Syntaksen for bruk av SUM()-funksjonen er som følger:

VELG SUM(kolonnenavn) FRA tabellnavn;

Vi vet navnet på kolonnen - summa, men vi har ikke navnet på tabellen, siden den er resultatet av en spørring. Hva å gjøre? For slike tilfeller har MySQL Views. En visning er en utvalgsspørring som får et unikt navn og kan lagres i en database for senere bruk.

Syntaksen for å lage en visning er som følger:

CREATE VIEW view_name AS-forespørsel;

La oss lagre forespørselen vår som en visning kalt 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= (VELG id_incoming WingHERE id_incoming WHERE id_incoming) );

6. Nå kan du bruke den endelige funksjonen SUM():

VELG SUM(summa) FRA rapportleverandør;

Så vi oppnådde resultatet, selv om vi for dette måtte bruke nestede spørringer, sammenføyninger, beregnede kolonner og visninger. Ja, noen ganger må du tenke for å få et resultat, uten dette kommer du ingen vei. Men vi kom inn på to svært viktige temaer – beregnede spalter og synspunkter. La oss snakke om dem mer detaljert.

Beregnede felt (kolonner)

Ved å bruke et eksempel så vi på et matematisk beregnet felt i dag. Her vil jeg legge til at du ikke bare kan bruke multiplikasjonsoperasjonen (*), men også subtraksjon (-), addisjon (+) og divisjon (/). Syntaksen er som følger:

SELECT kolonnenavn 1, kolonnenavn 2, kolonnenavn 1 * kolonnenavn 2 AS beregnet_kolonne_navn FRA tabellnavn;

Den andre nyansen er søkeordet AS, vi brukte det til å angi navnet på den beregnede kolonnen. Faktisk brukes dette nøkkelordet til å angi aliaser for alle kolonner. Hvorfor er dette nødvendig? For kodereduksjon og lesbarhet. For eksempel kan vårt syn se slik ut:

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= (VELG id_incoming FROM incoming WHERE id_vendor=2);

Enig i at dette er mye kortere og klarere.

Representasjon

Vi har allerede sett på syntaksen for å lage visninger. Når visninger er opprettet, kan de brukes på samme måte som tabeller. Det vil si, kjøre spørringer mot dem, filtrere og sortere data, og kombinere noen visninger med andre. På den ene siden er dette en veldig praktisk måte å lagre ofte brukte komplekse søk (som i vårt eksempel).

Men husk at visninger ikke er tabeller, det vil si at de ikke lagrer data, men bare henter dem fra andre tabeller. Derfor, for det første, når dataene i tabellene endres, vil presentasjonsresultatene også endres. Og for det andre, når en forespørsel sendes til en visning, søkes de nødvendige dataene, det vil si at ytelsen til DBMS reduseres. Derfor bør du ikke misbruke dem.

COMPUTING

Oppsummeringsfunksjoner

SQL-spørringsuttrykk krever ofte dataforbehandling. Til dette formål brukes spesielle funksjoner og uttrykk.

Ganske ofte må du finne ut hvor mange poster som samsvarer med et bestemt søk,hva er summen av verdiene til en viss numerisk kolonne, dens maksimums-, minimums- og gjennomsnittsverdier. Til dette formålet brukes de såkalte endelige (statistiske, aggregerte) funksjonene. Sammendragsfunksjoner behandler sett med poster spesifisert, for eksempel av en WHERE-klausul. Hvis du inkluderer dem i listen over kolonner etter en SELECT-setning, vil den resulterende tabellen inneholde ikke bare databasetabellkolonnene, men også verdiene beregnet av disse funksjonene. Følgende erliste over oppsummeringsfunksjoner.

COUNT (parameter ) returnerer antall poster spesifisert i parameteren. Hvis du ønsker å få antallet på alle poster, bør du angi stjerne (*)-symbolet som en parameter. Hvis du angir et kolonnenavn som en parameter, vil funksjonen returnere antall poster der denne kolonnen har andre verdier enn NULL. For å finne ut hvor mange forskjellige verdier en kolonne inneholder, gå foran kolonnenavnet med DISTINCT nøkkelordet. For eksempel:

VELG ANTALL(*) FRA klienter;

VELG ANTALL(Ordre_Beløp) FRA kunder;

SELECT COUNT(DISTINCT Order_Amount) FROM Customers;

Å prøve å kjøre følgende spørring vil resultere i en feilmelding:

SELECT Region , COUNT(*) FROM Clients ;

SUM (parameter ) returnerer summen av verdiene i kolonnen spesifisert i parameteren. Parameteren kan også være et uttrykk som inneholder navnet på kolonnen. For eksempel:

VELG SUM (Ordre_Beløp) FRA kunder;

Denne SQL-setningen returnerer en én-kolonne, én-posttabell som inneholder summen av alle definerte verdier for Order_Amount-kolonnen fra Kunder-tabellen.

La oss si at i kildetabellen er verdiene i kolonnen Order_Amount uttrykt i rubler, og vi må beregne det totale beløpet i dollar. Hvis gjeldende valutakurs for eksempel er 27,8, kan du få det nødvendige resultatet ved å bruke uttrykket:

VELG SUM (Ordrebeløp*27.8) FRA klienter;

AVG (parameter ) returnerer det aritmetiske gjennomsnittet av alle verdiene i kolonnen spesifisert i parameteren. Parameteren kan være et uttrykk som inneholder navnet på kolonnen. For eksempel:

VELG AVG (Order_Amount) FRA kunder;

VELG AVG (Order_Amount*27.8) FRA klienter

HVOR Region<>"North_3west";

MAX (parameter ) returnerer maksimumsverdien i kolonnen spesifisert i parameteren. Parameteren kan også være et uttrykk som inneholder navnet på kolonnen. For eksempel:

SELECT MAX(Order_Amount) FROM Clients;

VELG MAKS(Ordre_Beløp*27.8) FRA klienter

HVOR Region<>"North_3west";

MIN (parameter ) returnerer minimumsverdien i kolonnen spesifisert i parameteren. Parameteren kan være et uttrykk som inneholder navnet på kolonnen. For eksempel:

VELG MIN(Ordre_Beløp) FRA kunder;

VELG MIN (bestillingsbeløp*27,8) FRA klienter

HVOR Region<>"North_3west";

I praksis er det ofte nødvendig å få en endelig tabell som inneholder total-, gjennomsnitts-, maksimums- og minimumsverdier av numeriske kolonner. For å gjøre dette bør du bruke gruppering (GROUP BY) og oppsummeringsfunksjoner.

VELG region, SUM (Ordre_beløp) FRA kunder

GRUPPE ETTER Region;

Resultattabellen for denne spørringen inneholder navnene på regionene og de totale (totale) ordrebeløpene fra alle kunder fra de tilsvarende regionene (fig. 5).

Vurder nå en forespørsel om å få alle sammendragsdata etter region:

SELECT Region, SUM (Order_Amount), AVG (Ordre_amount), MAX(Order_amount), MIN (bestillingsbeløp)

FRA klienter

GRUPPE ETTER Region;

Original- og resultattabellene er vist i fig. 8. I eksemplet er bare Nordvest-regionen representert i kildetabellen med mer enn én post. Derfor, i resultattabellen for den, gir forskjellige oppsummeringsfunksjoner forskjellige verdier.

Ris. 8. Endelig tabell over ordrebeløp etter region

Når du bruker sammendragsfunksjoner på en kolonneliste i en SELECT-setning, er overskriftene til de tilsvarende kolonnene i resultattabellen Uttr1001, Uttr1002, og så videre. (eller noe lignende, avhengig av SQL-implementeringen). Du kan imidlertid angi overskrifter for verdiene til sammendragsfunksjoner og andre kolonner etter eget skjønn. For å gjøre dette, rett etter kolonnen i SELECT-setningen, spesifiser et uttrykk for skjemaet:

AS kolonneoverskrift

Nøkkelordet AS (as) betyr at i resultattabellen må den tilsvarende kolonnen ha en overskrift spesifisert etter AS. Den tildelte tittelen kalles også et alias. Følgende eksempel (figur 9) setter aliaser for alle beregnede kolonner:

VELG region,

SUM (Order_Amount) AS [Total Order Amount],

AVG (Order_Amount) AS [Average Order Amount],

MAX(Order_Amount) AS Maximum,

MIN (Ordre_beløp) AS minimum,

FRA klienter

GRUPPE ETTER Region;

Ris. 9. Endelig tabell over ordrebeløp etter region ved bruk av kolonnealiaser

Kallenavn som består av flere ord atskilt med mellomrom er omsluttet av hakeparenteser.

Sammendragsfunksjoner kan brukes i SELECT- og HAVING-klausuler, men de kan ikke brukes i WHERE-klausuler. HAVING-operatoren ligner på WHERE-operatoren, men i motsetning til WHERE velger den poster i grupper.

La oss si at du vil finne ut hvilke regioner som har mer enn én klient. For dette formålet kan du bruke følgende spørring:

SELECT Region , Count(*)

FRA klienter

GRUPPER ETTER Region SOM HAR ANTALL(*) > 1;

Verdibehandlingsfunksjoner

Når du arbeider med data må du ofte behandle det (konvertere det til ønsket form): velge en delstreng i en streng, fjerne innledende og etterfølgende mellomrom, runde et tall, beregne kvadratroten, bestemme gjeldende tid osv. SQL har følgende tre typer funksjoner:

strengfunksjoner;
numeriske funksjoner;
dato-tid funksjoner.

Strengefunksjoner

Stringfunksjoner tar en streng som en parameter og returnerer en streng eller NULL etter å ha behandlet den.

SUBSTRING (linje FRA start)returnerer en delstreng som er et resultat av strengen spesifisert som en parameter linje. Understreng begynner med tegnet hvis serienummer er spesifisert i startparameteren, og har lengden spesifisert i lengdeparameteren. Tegnene i strengen er nummerert fra venstre til høyre, med start fra 1. Firkantede parenteser her indikerer bare at uttrykket vedlagt i dem er valgfritt. Hvis uttrykket FOR lengde ikke brukes, da en delstreng fra Start og til slutten av den opprinnelige linjen. Parameterverdier start og lengde må velges slik at den søkte understrengen faktisk er inne i den opprinnelige strengen. Ellers vil SUBSTRING-funksjonen returnere NULL.

For eksempel:

SUBSTRING ("Kjære Masha!" FRA 9 FOR 4) returnerer "Masha";

SUBSTRING ("Kjære Masha!" FRA 9) returnerer "Masha!";

SUBSTRING("Kjære Masha!" FRA 15) returnerer NULL.

Du kan bruke denne funksjonen i et SQL-uttrykk, for eksempel slik:

VELG * FRA klienter

WHERE SUBSTRING(Region FRA 1 FOR 5) = "Nord";

UPPER(streng ) konverterer alle tegn i strengen spesifisert i parameteren til store bokstaver.
LOWER(streng ) konverterer alle tegn i strengen spesifisert i parameteren til små bokstaver.
TRIM (LEADING | TRAILING | BEGGE ["karakter"] FRA streng ) fjerner innledende (LEADING), etterfølgende (TRAILING) eller begge (BEDE) tegn fra en streng. Som standard er tegnet som skal fjernes et mellomrom (" "), så det kan utelates. Oftest brukes denne funksjonen til å fjerne mellomrom.

For eksempel:

TRIM (LEDENDE " " FRA "byen St. Petersburg") roterer "byen St. Petersburg";

TRIM(TRALING " " FRA "byen St. Petersburg") returnerer "byen St. Petersburg";

TRIM (BÅDE " " FRA " by St. Petersburg ") returnerer "by St. Petersburg";

TRIM(BEDE FRA " by St. Petersburg ") returnerer "by St. Petersburg";

TRIM(BEDE "g" FRA "byen St. Petersburg") returnerer "byen St. Petersburg".

Blant disse funksjonene er de mest brukte SUBSTRING() OG TRIM().

Numeriske funksjoner

Numeriske funksjoner kan godta data ikke bare av numerisk type som en parameter, men returnerer alltid et tall eller NULL (udefinert verdi).

POSISJON ( targetString IN-streng) søker etter en forekomst av målstrengen i den angitte strengen. Hvis søket er vellykket, returnerer posisjonsnummeret til det første tegnet, ellers 0. Hvis målstrengen har null lengde (for eksempel strengen " "), returnerer funksjonen 1. Hvis minst én av parameterne er NULL , så returneres NULL. Linjetegn er nummerert fra venstre til høyre, fra 1.

For eksempel:

POSITION("e" IN "Hei alle sammen") returnerer 5;

POSISJON ("alle" I "Hei alle sammen") returnerer 8;

POSITION(" " Hei alle sammen") returnerer 1;

POSITION("Hei!" I "Hei alle sammen") returnerer 0.

I klienttabellen (se fig. 1) inneholder kolonnen Adresse, i tillegg til bynavn, postnummer, gatenavn og andre data. Du må kanskje velge poster for kunder som bor i en bestemt by. Så hvis du vil velge poster relatert til klienter som bor i St. Petersburg, kan du bruke følgende SQL-spørringsuttrykk:

VELG * FRA klienter

HVOR POSISJON (" St. Petersburg " IN-adresse ) > 0;

Merk at denne enkle forespørselen om datainnhenting kan formuleres annerledes:

VELG * FRA klienter

WHERE Adresse SOM "%Petersburg%";

UTTREKK (parameter ) trekker ut et element fra en dato-tidsverdi eller fra et intervall. For eksempel:

UTTREKK (MÅNED FRA DATO "2005-10-25") returnerer 10.

CHARACTER_LENGTH(streng ) returnerer antall tegn i strengen.

For eksempel:

CHARACTER_LENGTH("Hei alle sammen") returnerer 11.

OCTET_LENGTH(streng ) returnerer antall oktetter (byte) i strengen. Hvert latinske eller kyrilliske tegn er representert med én byte, og det kinesiske alfabettegnet er representert med to byte.
KARDINALITET (parameter ) tar en samling av elementer som en parameter og returnerer antall elementer i samlingen (kardinalnummer). En samling kan for eksempel være en matrise eller et multisett som inneholder elementer av forskjellige typer.
ABS (nummer ) returnerer den absolutte verdien av et tall. For eksempel:

ABS (-123) returnerer 123;

ABS (2 - 5) returnerer 3.

MO D (nummer1, nummer2 ) returnerer resten av en heltallsdivisjon av det første tallet med det andre. For eksempel:

MOD(5; h) returnerer 2;

MOD(2, h) returnerer 0.

LN (nummer ) returnerer den naturlige logaritmen til et tall.
EXP (tall) returnerer tallet (grunnlaget til den naturlige logaritmen i potensen av tall).
POWER (nummer1, nummer2 ) returnerer nummer1 Nummer 2 (tall1 i potens av tall2).
SQRT (tall ) returnerer kvadratroten av et tall.
ETASJE (nummer ) returnerer det største heltall som ikke overstiger det som er spesifisert av parameteren (avrunding nedover). For eksempel:

FLOOR (5.123) returnerer 5.0.

CEIL (nummer) eller CEILING (nummer ) returnerer det minste heltall som ikke er mindre enn verdien spesifisert av round up-parameteren). For eksempel:

CEIL(5,123) returnerer 6,0.

WIDTH_BUCKET (tall1, tall2, tall3, tall4) returnerer et heltall i området mellom 0 og tall4 + 1. Parameterne tall2 og tall3 spesifiserer et numerisk intervall delt inn i like intervaller, hvor antallet er spesifisert av parameteren number4. Funksjonen bestemmer nummeret på intervallet der verdien faller nummer1. Hvis tall1 er utenfor det angitte området, returnerer funksjonen 0 eller tall 4 + 1. For eksempel:

WIDTH_BUCKET(3.14; 0; 9; 5) returnerer 2.

Dato-tid funksjoner

SQL har tre funksjoner som returnerer gjeldende dato og klokkeslett.

DAGENS DATO returnerer gjeldende dato (skriv DATO).

For eksempel: 2005-06-18.

CURRENT_TIME (tall ) returnerer gjeldende tid (TID-type). Heltallsparameteren spesifiserer presisjonen til sekundrepresentasjonen. For eksempel vil en verdi på 2 representere sekunder til nærmeste hundredel (to desimaler):

12:39:45.27.

CURRENT_TIMESTAMP (tall ) returnerer datoen og klokkeslettet (Tidsstempel-type). For eksempel, 2005-06-18 12:39:45.27. Heltallsparameteren spesifiserer presisjonen til sekundrepresentasjonen.

Merk at datoen og klokkeslettet som returneres av disse funksjonene ikke er en tegntype. Hvis du vil representere dem som tegnstrenger, bør du bruke konverteringsfunksjonen CAST() for å gjøre dette.

Dato-tidsfunksjoner brukes ofte i spørringer for å sette inn, oppdatere og slette data. For eksempel, når du registrerer salgsinformasjon, legges gjeldende dato og klokkeslett inn i kolonnen som er gitt for dette formålet. Etter å ha summert resultatene for en måned eller et kvartal, kan salgsdata for rapporteringsperioden slettes.

Beregnet uttrykk

Beregnet uttrykk bygges fra konstanter (numeriske, streng, logiske), funksjoner, feltnavn og andre typer data ved å koble dem med aritmetiske, strenge, logiske og andre operatorer. I sin tur kan uttrykk kombineres ved hjelp av operatorer til mer komplekse (sammensatte) uttrykk. Parenteser brukes til å kontrollere rekkefølgen uttrykkene evalueres i.

Logiske operatører OG, ELLER og IKKE og funksjoner har vært diskutert tidligere.

Aritmetiske operatorer:

+ tillegg;
- subtraksjon;
* multiplikasjon;
/ divisjon.

Strengeoperatorbare én sammenkoblings- eller strengsammenkoblingsoperator (| |). Noen implementeringer av SQL (som Microsoft Access) bruker tegnet (+) i stedet for (| |). Sammenkoblingsoperatoren legger til den andre strengen til slutten av det første eksemplet, uttrykket:

"Sasha" | | "elsker" | | "Vinker"

vil returnere strengen "Sasha elsker Masha" som et resultat.

Når du komponerer uttrykk, må du sørge for at operandene til operatorene er av gyldige typer. For eksempel er uttrykket: 123 + "Sasha" ikke gyldig fordi den aritmetiske addisjonsoperatoren brukes på en strengoperand.

Beregnede uttrykk kan vises etter en SELECT-setning, så vel som i tilstandsuttrykk for WHERE- og HAVI-setninger N.G.

La oss se på noen få eksempler.

La Sales-tabellen inneholde kolonnene ProductType, Quantity og Price, og vi vil vite inntektene for hver produkttype. For å gjøre dette, inkluderer du bare uttrykket Kvantitet*Pris i listen over kolonner etter SELECT-setningen:

VELG Product_type, Quantity, Price, Quantity*Pris SOM

Totalt FRA Salg;

Dette bruker nøkkelordet AS (som) for å spesifisere et alias for den beregnede datakolonnen.

I fig. Figur 10 viser den opprinnelige salgstabellen og spørringsresultattabellen.

Ris. 10. Resultat av spørringen med beregning av inntekter for hver type produkt

Hvis du vil finne ut den totale inntekten fra salget av alle varer, bruker du bare følgende spørring:

VELG SUM (Antall*Pris) FRA Salg;

Følgende spørring inneholder beregnede uttrykk i både kolonnelisten og betingelsen til WHERE-leddet. Han velger fra salgstabellen de produktene som har en salgsinntekt på mer enn 1000:

VELG Product_type, Mengde*Pris AS Total

FRA Salg

HVOR Antall*Pris > 1000;

La oss anta at du ønsker å få en tabell som har to kolonner:

Produkt som inneholder produkttype og pris;

Totalt som inneholder inntekter.

Siden det i den opprinnelige salgstabellen antas at kolonnen Product_Type er tegn (CHAR-type) og Pris-kolonnen er numerisk, er det nødvendig å støpe den numeriske typen til en tegntype ved sammenslåing (liming) av data fra disse kolonnene. CAST() funksjon. Spørringen som utfører denne oppgaven ser slik ut (fig. 11):

FRA Salg;

Ris. 11. Resultat av en spørring som kombinerer ulike typer data i én kolonne

Merk. I Microsoft Access vil en lignende spørring se slik ut:

VELG Product_type + " (Pris: " + C Str (Pris) + ")" AS Produkt,

Mengde*Pris AS Totalt

FRA Salg;

Betingede uttrykk med CASE-erklæring

Konvensjonelle programmeringsspråk har betingede hoppoperatorer som lar deg kontrollere beregningsprosessen avhengig av om en betingelse er sann eller ikke. I SQL er denne operatoren CASE (sak, omstendighet, instans). I SQL:2003 returnerer denne operatoren en verdi og kan derfor brukes i uttrykk. Den har to hovedformer, som vi skal se på i denne delen.

CASE-uttalelse med verdier

CASE-setningen med verdier har følgende syntaks:

CASE checked_value

NÅR verdi1 SÅ resultat1

NÅR verdi2 SÅ resultat2

. . .

NÅR verdien av N SÅ resultatet av N

ANDRE resultatX

I tilfelle sjekket_verdi er lik verdi1 , returnerer CASE-setningen verdien resultat1 , spesifisert etter THEN-nøkkelordet. Ellers sammenlignes checked_value med verdi2 , og hvis de er like, returneres verdien resultat2. Ellers sammenlignes verdien som testes med neste verdi spesifisert etter nøkkelordet WHEN osv. Hvis testet_verdi ikke er lik noen av disse verdiene, returneres verdien resultat X , spesifisert etter nøkkelordet ELSE (else).

Nøkkelordet ELSE er valgfritt. Hvis den mangler og ingen av verdiene som sammenlignes er lik verdien som testes, returnerer CASE-setningen NULL.

La oss si, basert på klienttabellen (se fig. 1), vil du få en tabell der navnene på regionene er erstattet med kodenumrene deres. Hvis det ikke er for mange forskjellige regioner i kildetabellen, er det praktisk å bruke en spørring med CASE-operatøren for å løse dette problemet:

VELG navn, adresse,

CASE-regionen

NÅR "Moskva" SÅ "77"

NÅR "Tver region" SÅ "69"

. . .

ELSE Region

AS Regionkode

FRA klienter;

CASE-uttalelse med søkebetingelser

Den andre formen for CASE-operatøren involverer bruken av den når du søker i en tabell etter de postene som tilfredsstiller en viss betingelse:

SAK

NÅR tilstand1 SÅ resultat1

NÅR fangst2 SÅ resultat2

. . .

NÅR tilstand N SÅ resultat N

ANDRE resultatX

CASE-setningen tester om betingelse1 er sann for den første posten i settet definert av WHERE-leddet, eller hele tabellen hvis WHERE ikke er til stede. Hvis ja, returnerer CASE resultat1. Ellers kontrolleres condition2 for denne posten. Hvis det er sant, returneres verdien resultat2 osv. Hvis ingen av betingelsene er sanne, returneres verdiresultatet X , spesifisert etter ELSE-nøkkelordet.

Nøkkelordet ELSE er valgfritt. Hvis den mangler og ingen av betingelsene er sanne, roterer CASE-setningen NULL. Etter at setningen som inneholder CASE er utført for den første posten, går den videre til neste post. Dette fortsetter til hele settet med poster er behandlet.

Anta at i en boktabell (tittel, pris) er en kolonne NULL hvis den tilsvarende boken er utsolgt. Følgende spørring returnerer en tabell som viser «Ikke på lager» i stedet for NULL:

VELG tittel,

SAK

NÅR prisen ER NULL, SÅ "Ikke på lager"

ANDEN CAST(Pris SOM CHAR(8))

AS Pris

FRA bøker;

Alle verdier i samme kolonne må være av samme type. Derfor bruker denne spørringen CAST-typekonverteringsfunksjonen til å caste de numeriske verdiene i Pris-kolonnen til en tegntype.

Merk at du alltid kan bruke den andre formen av CASE-setningen i stedet for den første:

SAK

NÅR testet_verdi = verdi1 SÅ resultat1

NÅR testet_verdi = verdi2 SÅ resultat2

. . .

NÅR sjekket_verdi = verdi N SÅ resultatN

ANDRE resultat

NULLIF og COALESCE funksjoner

I noen tilfeller, spesielt i forespørsler om å oppdatere data (UPDATE-operator), er det praktisk å bruke de mer kompakte funksjonene NULLIF() (NULL if) og COALESCE() (combine) i stedet for den tungvinte CASE-operatoren.

NULLIF funksjon ( verdi1, verdi2) returnerer NULL hvis verdien av den første parameteren samsvarer med verdien til den andre parameteren; i tilfelle mismatch, returneres verdien til den første parameteren uendret. Det vil si at hvis likhetsverdi1 = verdi2 er sann, returnerer funksjonen NULL, ellers verdi verdi1.

Denne funksjonen tilsvarer CASE-setningen i følgende to former:

CASE-verdi1

NÅR verdi2 SÅ NULL

ANDRE verdi1

NÅR verdi1 = verdi2 SÅ NULL

ANDRE verdi1

Funksjon COALESCE( verdi1, verdi2, ... , N verdi) godtar en liste med verdier, som kan være enten NULL eller NULL. Funksjonen returnerer en spesifisert verdi fra en liste eller NULL hvis alle verdier er udefinerte.

Denne funksjonen tilsvarer følgende CASE-setning:

SAK

NÅR verdi 1 IKKE ER NULL, SÅ verdi 1

NÅR verdi 2 IKKE ER NULL, SÅ verdi 2

. . .

NÅR verdien N IKKE ER NULL, SÅ verdi N

ANNET NULL

Anta at i tabellen Bøker (tittel, pris) er Pris-kolonnen NULL hvis den tilsvarende boken er utsolgt. Følgende spørring returnerer en tabell hvor i stedet for NULL Teksten "Ikke på lager" vises:

SELECT Name, COALESCE (CAST(Price AS CHAR(8)),

"Ikke på lager") AS Pris

FRA bøker;

SUM-funksjonen i SQL-språket, til tross for sin enkelhet, brukes ganske ofte når du arbeider med en database. Med dens hjelp er det praktisk å oppnå noen mellomliggende eller endelige resultater uten å ty til hjelp av ekstra DBMS-verktøy.

Funksjonssyntaks

I de fleste SQL-språk er sumsyntaksen den samme - bare navnet på feltet eller en aritmetisk operasjon av flere av dem brukes som et argument, som summen kreves over.

I unntakstilfeller er det mulig å overføre en spesifikk verdi som et tall eller en variabel, men slike "ordninger" brukes praktisk talt ikke, siden de ikke har mye verdi. Nedenfor er funksjonssyntaksen i SQL:

sum(a) - her brukes en eller annen numerisk verdi eller uttrykk som parameter a

Det er verdt å merke seg at før parameteren kan du angi nøkkelord, for eksempel DISTINCT eller ALL, som vil ta henholdsvis unike eller alle verdier.

Eksempel på bruk av SUM i SQL

For fullt ut å forstå hvordan funksjonen fungerer, er det verdt å vurdere flere eksempler. I SQL kan SUM brukes både som et returresultat og som en mellomverdi, for eksempel for å teste en betingelse.

For det første tilfellet, vurder alternativet når du trenger å returnere salgsbeløpet for hvert produkt, med tanke på at antall kjøp kan være i flertall. For å få resultatet, vil det være nok å kjøre følgende spørring:

VELG Produkt, sum(PurchaseAmount) FRA Salgsgruppe Etter produkt;

Svaret på denne kommandoen vil være en unik liste over produkter med det totale kjøpsbeløpet for hver av dem.

For det andre eksemplet må du få en liste over produkter hvis salgsbeløp oversteg en viss verdi, for eksempel 100. Du kan få resultatet for denne oppgaven på flere måter, den mest optimale er å utføre en forespørsel:

VELG produkt FRA (VELG produkt, sum(kjøpsbeløp) som beløp FRA salg) HVOR Sum > 100.