Функцията SUM в SQL език, въпреки своята простота, се използва доста често при работа с база данни. С негова помощ е удобно да се получат някои междинни или крайни резултати, без да се прибягва до помощта на помощни инструменти за СУБД.

Синтаксис на функцията

В повечето SQL езици синтаксисът на сумата е един и същ — само името на полето или някаква аритметична операция на няколко от тях се използва като аргумент, върху който се изисква сумиране.

В изключителни случаи можете да прехвърлите конкретна стойност под формата на число или променлива, но такива "схеми" практически не се използват, тъй като не носят голяма стойност. По-долу е синтаксисът на функцията on SQL език:

sum (a) - тук някаква числова стойност или израз се използва като параметър

Струва си да се отбележи, че преди параметъра можете да зададете ключови думи, например DISTINCT или ALL, които ще приемат съответно само уникални или всички стойности.

Пример за използване на SUM в SQL

За окончателно разбиране как работи функцията, си струва да разгледаме няколко примера. В SQL SUM може да се използва както като върнат резултат, така и като междинна стойност, например за проверка на условие.

За първия случай помислете за опцията, когато искате да върнете сумата на продажбите за всеки продукт, като се има предвид, че броят на направените покупки може да бъде в множествено число. За да получите резултата, ще бъде достатъчно да изпълните следната заявка:

ИЗБЕРЕТЕ продукт, сума (сума на покупката) ОТ Продажби, група по продукт;

Отговорът на тази командаще има уникален списък с продукти с общата сума на покупките за всеки от тях.

За втория пример трябва да получите списък с продукти, за които сумата на продажбите е надвишила определена стойност, например 100. Има няколко начина да получите резултата за тази задача, най-оптималният от които е изпълнение на една заявка:

ИЗБЕРЕТЕ артикул ОТ (ИЗБЕРЕТЕ артикул, сума (сума на покупката) като ОТ сума на продажбите) КЪДЕ Сума> 100.

ИЗЧИСЛЕНИЯ

Обобщени функции

В изразите на SQL заявка често трябва да се изпълняват предварителна обработкаданни. За тази цел се използват специални функции и изрази.

Доста често трябва да разберете колко записа съответстват на конкретна заявка,каква е сумата от стойностите на определена числова колона, нейните максимални, минимални и средни стойности. За това се използват т. нар. обобщаващи (статистически, агрегатни) функции. Обобщаващите функции обработват набори от записи, посочени от клауза WHERE, например. Ако те са включени в списъка с колони след оператора SELECT, получената таблица ще съдържа не само колоните на таблицата на базата данни, но и стойностите, изчислени с помощта на тези функции. Следното есписък с обобщаващи функции.

• COUNT (параметър ) - връща броя на записите, посочени в параметъра. Ако искате да получите броя на всички записи, трябва да посочите звездичка (*) като параметър. Ако посочите име на колона като параметър, функцията ще върне броя записи, в които тази колона има стойности, различни от NULL. За да разберете колко различни стойности съдържа една колона, предхождайте името й с ключовата дума DISTINCT. Например:

ИЗБЕРЕТЕ БРОЙ (*) ОТ клиенти;

ИЗБЕРЕТЕ БРОЙ (сума на поръчката) ОТ клиенти;

ИЗБЕРЕТЕ БРОЙ (DISTINCT сума_на поръчка) ОТ клиенти;

Опитът за изпълнение на следната заявка ще доведе до съобщение за грешка:

ИЗБЕРЕТЕ Регион, БРОЙ (*) ОТ клиенти;

• SUM (параметър ) - връща сумата от стойностите на колоната, посочена в параметъра. Параметърът може да бъде и израз, съдържащ името на колоната. Например:

ИЗБЕРЕТЕ СУМА (Order_Sum) ОТ Клиенти;

Този SQL израз връща таблица с една колона и един запис, съдържаща сумата от всички дефинирани стойности в колоната Order_Sum от таблицата Клиенти.

Да предположим, че в изходната таблица стойностите в колоната Order_Amount са изразени в рубли и искаме да изчислим общата сума в долари. Ако текущият обменен курс е например 27,8, тогава можете да получите необходимия резултат, като използвате израза:

ИЗБЕРЕТЕ СУМА (Order_Sum * 27.8) ОТ Клиенти;

• AVG (параметър ) - връща средноаритметичната стойност на всички стойности на колоната, посочена в параметъра. Параметърът може да бъде израз, съдържащ името на колоната. Например:

ИЗБЕРЕТЕ AVG (сума на поръчката) ОТ клиенти;

ИЗБЕРЕТЕ AVG (Sum_Order_Sum * 27.8) ОТ клиенти

КЪДЕ Регион<>"Север_3 запад";

• MAX (параметър ) - връща максималната стойност в колоната, посочена в параметъра. Параметърът може да бъде и израз, съдържащ името на колоната. Например:

SELECT MAX (сума_поръчка) ОТ клиенти;

SELECT MAX (Order_Sum * 27.8) ОТ КЛИЕНТИ

КЪДЕТО регион<>"Север_3 запад";

• MIN (параметър ) - връща минималната стойност в колоната, посочена в параметъра. Параметърът може да бъде израз, съдържащ името на колоната. Например:

SELECT MIN (Order_Sum) ОТ клиенти;

ИЗБЕРЕТЕ МИН (сума_поръчка * 27.8) ОТ клиенти

КЪДЕТО регион<>"Север_3 запад";

На практика често се изисква да се получи обобщена таблица, съдържаща общите, средните, максималните и минималните стойности на числовите колони. За да направите това, трябва да използвате групиране (GROUP BY) и функции за обобщение.

ИЗБЕРЕТЕ Регион, SUM (Сума_поръчка) ОТ Клиенти

ГРУПА ПО Регион;

Таблицата с резултати за тази заявка съдържа имената на регионите и общата (обща) сума на поръчките на всички клиенти от съответните региони (фиг. 5).

Сега нека разгледаме заявка за получаване на всички суми по региони:

ИЗБЕРЕТЕ Регион, SUM (Поръчка_Sum), AVG (сума_поръчка), MAX (сума_поръчка), MIN (сума_поръчка)

ОТ КЛИЕНТИ

ГРУПА ПО Регион;

Началната и крайната таблици са показани на фиг. 8. В примера само северозападният регион е представен в оригиналната таблица с повече от един запис. Следователно в таблицата с резултати за него различните обобщаващи функции дават различни стойности.

Ориз. 8. Обобщена таблица на сумите на поръчките по региони

Когато използвате обобщаващи функции в списъка с колони в оператора SELECT, заглавията на съответните колони в таблицата с резултати са Expr1001, Expr1002 и т.н. (или нещо подобно, в зависимост от SQL реализацията). Можете обаче да зададете заглавията за стойностите на обобщаващите функции и други колони, както желаете. За да направите това, достатъчно е да посочите израз на формата след колоната в оператора SELECT:

AS column_header

Ключовата дума AS (as) означава, че в таблицата с резултати съответната колона трябва да има заглавието, посочено след AS. Присвоеното заглавие се нарича още псевдоним. Следният пример (Фигура 9) задава псевдоними за всички изчислени колони:

ИЗБЕРЕТЕ регион,

СУМ (Сума_на поръчка) AS [Обща поръчка],

AVG (Сума_на поръчка) AS [Средна сума на поръчката],

MAX (сума_на поръчка) AS Максимум,

МИН (сума_на поръчка) AS минимум,

ОТ КЛИЕНТИ

ГРУПА ПО Регион;

Ориз. 9. Обобщена таблица на сумите на поръчките по региони с помощта на псевдоними на колони

Псевдонимите с множество думи, разделени с интервали, са затворени в квадратни скоби.

Функциите за обобщение могат да се използват в клаузи SELECT и HAVING, но не могат да се използват в клаузи WHERE. Една клауза HAVING е подобна на клаузата WHERE, но за разлика от клаузата WHERE избира записи в групи.

Да приемем, че искате да определите кои региони имат повече от един клиент. За целта можете да използвате следната заявка:

ИЗБЕРЕТЕ регион, брой (*)

ОТ КЛИЕНТИ

ГРУПА ПО РЕГИОН С БРОЙ (*)> 1;

Функции за обработка на стойности

Когато работите с данни, често се налага да ги обработвате (да го преобразувате в желаната форма): избирате подниз в низ, премахвате водещи и крайни интервали, закръглявате число, изчислявате квадратния корен, определяте текущото време и т.н. Има три типа функции в SQL:

• низови функции;
• числови функции;
• функции за дата и час.

Функции на низове

Функциите за низ приемат низ като параметър и връщат низ или NULL след обработката му.

• SUBSTRING (ОТ началото на реда)- връща поднизът, получен от низа, посочен като параметърлиния . Подниз започва със знак, чийто пореден номер е посочен в началния параметър и има дължина, посочена в параметъра за дължина. Линейните знаци са номерирани отляво надясно, започвайки от 1. Квадратните скоби тук показват само, че изразът, затворен в тях, е незадължителен. Ако изразътЗА дължина не се използва, тогава поднизът отЗапочнете до края на оригиналния ред. Стойности на параметритеначало и дължина трябва да бъде избран така, че желаният подниз действително да е вътре в оригиналния низ. В противен случай функцията SUBSTRING ще върне NULL.

Например:

SUBSTRING ("Скъпа Маша!" ОТ 9 ЗА 4) - връща "Маша";

SUBSTRING ("Скъпа Маша!" ОТ 9) - връща "Маша!";

SUBSTRING ("Скъпа Маша!" ОТ 15) — връща NULL.

Можете да използвате тази функция в SQL израз, например, така:

ИЗБЕРЕТЕ * ОТ Клиенти

WHERE SUBSTRING (Регион ОТ 1 ЗА 5) = "Север";

• UPPER (струн ) - преобразува всички знаци от посочения низ в главни букви.
• ДОЛНА (линия ) - преобразува всички знаци от посочения низ в малки букви.
• ОТРЕЖАВАНЕ (ВОДЕЩ | ЗАДАЧЕН | И ДВАТА ["знак"] ОТ низ ) - премахва началния (LEADING), крайния (TRAILING) или и двата (BOTH) знака от низа. Знакът по подразбиране, който трябва да се изтрие, е интервал (""), така че можете да го оставите празно. Тази функция най-често се използва за премахване на интервали.

Например:

TRIM (ВОДЕЩ "" ОТ "град Санкт Петербург") завърта "град Санкт Петербург";

TRIM (TRALING "" ОТ "град Санкт Петербург") връща "град Санкт Петербург";

TRIM (ДВАТА "" ОТ "град Санкт Петербург") - връща "град Санкт Петербург";

TRIM (ДВЕТЕ ОТ "град Санкт Петербург") - връща "град Санкт Петербург";

TRIM (ДВЕТЕ "r" ОТ "град Санкт Петербург") - връща "град Санкт Петербург".

Сред тези функции най-често използваните са SUBSTRING () и TRIM ().

Числови функции

Числовите функции като параметър могат да приемат данни не само от числов тип, но винаги връщат число или NULL (недефинирана стойност).

• ПОЗИЦИЯ ( целеви низ В низ) - търси среща на целевия низ в посочения низ. Ако търсенето е успешно, то връща номера на позицията на първия си знак, в противен случай - 0. Ако целевият низ има нулева дължина (например низът ""), тогава функцията връща 1. Ако поне един от параметрите е NULL, след което се връща NULL. Линейните знаци са номерирани отляво надясно, като се започне от 1.

Например:

ПОЗИЦИЯ ("e" В "Здравейте всички") - връща 5;

ПОЗИЦИЯ ("всички" В "Здравейте всички") - връща 8;

POSITION ("" Здравейте на всички ") - връща 1;

ПОЗИЦИЯ ("Здравейте!" В "Здравейте на всички") - Връща 0.

В таблицата Клиенти (виж фиг. 1) колоната Адрес съдържа освен името на града, пощенския код, името на улицата и други данни. Може да се наложи да изберете записи за клиенти, които живеят в конкретен град. Така че, ако искате да изберете записи, свързани с клиенти, живеещи в Санкт Петербург, можете да използвате следния израз на SQL заявка:

ИЗБЕРЕТЕ * ОТ Клиенти

КЪДЕ ПОЗИЦИЯ ("Санкт Петербург" В Адрес)> 0;

Имайте предвид, че тази проста заявка за извличане на данни може да бъде формулирана по различен начин:

ИЗБЕРЕТЕ * ОТ Клиенти

КЪДЕ Адрес КАТО "% Petersburg%";

• ИЗВЛЕЧВАНЕ (параметър ) - извлича елемент от стойност за дата и час или от интервал. Например:

ЕКСТРАКТ (МЕСЕЦ ОТ ДАТА "2005-10-25") -връща 10.

• CHARACTER_LENGTH (низ ) - връща броя на знаците в низ.

Например:

CHARACTER_LENGTH („Здравейте на всички“) – Връща 11.

• OCTET_LENGTH (низ ) - връща броя на октетите (байтовете) в низ. Всеки символ на латиница или кирилица е представен с един байт, а китайски символ е представен с два байта.
• КАРДИНАЛНОСТ (параметър ) - приема колекция от елементи като параметър и връща броя на елементите в колекцията (кардинал). Колекция може да бъде, например, масив или мултинабор, съдържащ елементи от различни типове.
• ABS (номер ) - връща абсолютната стойност на число. Например:

ABS (-123) - връща 123;

ABS (2 - 5) - Връща 3.

• MO D (номер 1, номер 2 ) - връща остатъка от целочисленото деление на първото число на второто. Например:

MOD (5, h) - връща 2;

MOD (2, h) - Връща 0.

• LN (номер ) - Връща естествения логаритъм на число.
• EXP (число) - връща e числото (основа на естествен логаритъм към степен на число).
• МОЩНОСТ (номер 1, номер 2 ) - връща номер1номер 2 (число 1 на степен на число 2).
• SQRT (брой ) - Връща корен квадратен от число.
• ЕТАЖ (брой ) - връща най-голямото цяло число, което не надвишава посочения параметър (закръгляване надолу). Например:

FLOOR (5.123) - Връща 5.0.

• CEIL (номер) или CEILING (брой ) - връща най-малкото цяло число, което не е по-малко дадено от параметъразакръгляване). Например:

CEIL (5.123) - Връща 6.0.

• WIDTH_BUCKET (число1, число2, число3, число4) връща цяло число в диапазона между 0 и число4 + 1. Параметрите number2 и number3 определят числов сегмент, разделен на равни интервали, чийто брой се определя от параметъра номер 4. Функцията определя номера на интервала, в който стойността попада номер1. Ако номер1 е извън посочения диапазон, тогава функцията връща 0 или число 4 + 1. Например:

WIDTH_BUCKET (3.14, 0, 9, 5) - Връща 2.

Функции за дата и час

Има три функции в SQL, които връщат текущата дата и час.

• ТЕКУЩА ДАТА - връща текущата дата (тип ДАТА).

Например: 2005-06-18.

• CURRENT_TIME (брой ) - връща текущото време (тип TIME). Целочислен параметър определя точността на секундите. Например, ако стойността е 2, секундите ще бъдат представени със стотна точност (две цифри в дробната част):

12:39:45.27.

• CURRENT_TIMESTAMP (число ) - връща дата и час (тип TIMESTAMP). Например, 2005-06-18 12:39:45.27. Целочислен параметър определя точността на секундите.

Имайте предвид, че датата и часът, върнати от тези функции, са от несимволен тип. Ако трябва да ги представите като низове от знаци, тогава трябва да използвате функцията за преобразуване от типа CAST ().

Функциите за дата и час обикновено се използват в заявки за вмъкване, актуализиране и изтриване на данни. Например, когато записвате информация за продажбите, текущата дата и час се въвеждат в специално предоставена колона. След сумиране на сумите за месеца или тримесечието, данните за продажбите за отчетния период могат да бъдат изтрити.

Оценени изрази

Изчислените изрази се изграждат от константи (числови, низови, логически), функции, имена на полета и други типове данни чрез свързването им с аритметични, низови, логически и други оператори. От своя страна изразите могат да бъдат комбинирани от оператори в по-сложни (съставни) изрази. Скобите се използват за контролиране на реда, в който се оценяват изразите.

Логически операториИ, ИЛИ и НЕ и функции бяха обсъдени по-рано.

Аритметични оператори:

• + - добавяне;
• - - изваждане;
• * - умножение;
• / - деление.

Низов операторсамо един е операторът за конкатенация или конкатенация (| |). В някои SQL реализации(например Microsoft Access) вместо (| |) се използва символът (+). Операторът за конкатенация добавя втория ред към края на първия пример, израз:

"Саша" | | "обича" | | "Маша"

като резултат ще върне низа „Саша обича Маша“.

Когато съставяте изрази, трябва да се уверите, че операндите на операторите имат валидни типове. Например изразът: 123 + "Sasha" е невалиден, защото операторът за аритметично събиране се прилага към операнда на низа.

Оценените изрази могат да се появят след израза SELECT, както и в условните изрази на клаузите WHERE и HAVI NG.

Нека разгледаме няколко примера.

Да предположим, че таблицата Продажби съдържа колоните Тип_артикул, Количество и Цена и трябва да знаем приходите за всеки тип продукт. За да направите това, достатъчно е да включите израза Количество * Цена в списъка с колони след оператора SELECT:

ИЗБЕРЕТЕ Тип_артикул, Количество, Цена, Количество * ЦенаКАТО

Общо ОТ продажби;

Той използва ключовата дума AS (as), за да посочи псевдоним за колоната с изчислени данни.

На фиг. 10 показва оригиналната таблица за продажби и получената таблица със заявки.

Ориз. 10. Резултат от запитване с изчисляване на приходите за всеки вид продукт

Ако трябва да разберете общия приход от продажбата на всички стоки, тогава е достатъчно да приложите следната заявка:

ИЗБЕРЕТЕ СУМА (Количество * Цена) ОТ Продажби;

Следната заявка съдържа оценени изрази както в списъка с колони, така и в клаузата WHERE. Той избира от таблицата за продажби тези стоки, приходите от продажба на които са повече от 1000:

ИЗБЕРЕТЕ тип_артикул, количество * Цена КАТО Общо

ОТ Продажби

КЪДЕ Количество * Цена> 1000;

Да предположим, че искате да получите таблица с две колони:

Продукт, съдържащ вид и цена на продукта;

Общо съдържащи приходи.

Тъй като се приема, че в оригиналната таблица за продажби колоната Item_type е символ (от тип CHAR), а колоната Price е числова, когато обединявате (слепвате) данни от тези колони, трябва да прехвърлите числовия тип към знак, използвайки CAST () функция. Заявката, изпълняваща тази задача, изглежда така (фиг. 11):

ИЗБЕРЕТЕ Тип_елемент | | "(Цена:" | | CAST (Цена КАТО CHAR (5)) | | ")" КАТО продукт, количество * Цена КАТО Общо

ОТ Продажби;

Ориз. 11. Резултат от заявка с комбиниране на различни типове данни в една колона

Забележка. В Microsoft Access подобна заявка ще изглежда така:

ИЗБЕРЕТЕ Тип_артикул + "(Цена:" + Cул (Цена) + ")" AS продукт,

Количество * Цена като Общо

ОТ Продажби;

Условни изрази с оператор CASE

В обикновените езици за програмиране има оператори за условно прескачане, които ви позволяват да контролирате изчислителния процес в зависимост от това дали определено условие е изпълнено или не. В SQL такъв израз е CASE (случай, обстоятелство, инстанция). В SQL: 2003 този оператор връща стойност и следователно може да се използва в изрази. Той има две основни форми, които ще разгледаме в този раздел.

Инструкция CASE със стойности

Инструкцията CASE със стойности има следния синтаксис:

CASE контролна_стойност

КОГА стойност1 ТОГА резултат1

КОГА стойност2 ТОГА резултат2

. . .

КОГА стойност N ТОГА резултат N

ELSE резултат X

В случай, когато проверена_стойностравно на стойност 1 , операторът CASE връща стойносттарезултат1 посочено след ключовата дума THEN (тогава). В противен случай checked_value се сравнява сстойност 2 , и ако са равни, тогава резултат2 се връща. В противен случай проверената стойност се сравнява със следващата стойност, посочена след ключовата дума WHEN (кога) и т. н. Ако checked_value не е равна на нито една от тези стойности, тогава стойността се връщарезултат X посочено след ключовата дума ELSE (в противен случай).

Ключовата дума ELSE не е задължителна. Ако тя отсъства и нито една от стойностите, които трябва да се сравняват, не е равна на тестваната стойност, тогава операторът CASE връща NULL.

Да предположим, че въз основа на таблицата Клиенти (виж фиг. 1), искате да получите таблица, в която имената на регионите се заменят с техните кодови номера. Ако в началната таблица няма твърде много различни региони, тогава за решаване на този проблем е удобно да използвате заявка с оператора CASE:

ИЗБЕРЕТЕ име, адрес,

CASE Регион

КОГАТО "Москва" ТОГАВА "77"

КОГА "Тверска област" ТОГАВА "69"

. . .

ELSE регион

AS Регионален код

ОТ КЛИЕНТИ;

Изявление CASE с думи за търсене

Втората форма на оператора CASE предполага използването му при търсене в таблица за онези записи, които отговарят на определено условие:

СЛУЧАЙ

КОГА условие1 ТОГАВА резултат1

КОГА резултат2 ТОГАВА резултат2

. . .

КОГА условие N ТОГАВА резултат N

ELSE резултат X

Инструкцията CASE проверява дали условие1 е вярно за първия запис от набора, определен от клаузата WHERE, или в цялата таблица, ако няма WHERE. Ако е така, CASE връща резултат1. В противен случай условие 2 се проверява за този запис. Ако е вярно, тогава се връща стойността result2 и т. н. Ако нито едно от условията не е изпълнено, тогава стойността се връща.х посочена след ключовата дума ELSE.

Ключовата дума ELSE не е задължителна. Ако липсва и нито едно от условията не е изпълнено, операторът CASE се върти на NULL. След като изразът, съдържащ CASE, се изпълни за първия запис, той преминава към следващия запис. Това продължава, докато целият набор от записи не бъде обработен.

Например в таблицата с книги (Заглавие, Цена) колоната е NULL, ако съответната книга не е налична. Следната заявка връща таблица, която показва текста „Няма наличност“ вместо NULL:

ИЗБЕРЕТЕ име,

СЛУЧАЙ

КОГАТО ЦЕНАТА Е НУЛА, ТОГАВА "Изчерпан"

ELSE CAST (Цена КАТО ЧЕЛ (8))

AS Цена

ОТ КНИГИ;

Всички стойности в една и съща колона трябва да са от един и същи тип. Следователно, в това исканефункцията за преобразуване на типа CAST се използва за прехвърляне на числовите стойности на колоната Цена към тип символ.

Имайте предвид, че вместо първата форма на израза CASE, винаги можете да използвате втората:

СЛУЧАЙ

КОГА тест_стойност = стойност1 ТОГАВА резултат1

КОГА тест_стойност = стойност2 ТОГАВА резултат2

. . .

КОГА проверена_стойност = стойност N ТОГАВА резултатN

ELSE резултатX

Функции NULLIF и COALESCE

В някои случаи, особено при заявки за актуализиране на данни (изявление UPDATE), е удобно да се използват по-компактните функции NULLIF () (NULL if) и COALESCE () (комбиниране) вместо тромавия израз CASE.

функция NULLIF ( стойност1, стойност2) връща NULL, ако стойността на първия параметър съвпада със стойността на втория параметър, ако не съвпада, стойността на първия параметър се връща непроменена. Тоест, ако равенството value1 = value2 е изпълнено, тогава функцията връща NULL, в противен случай - стойност value1.

Тази функция е еквивалентна на израза CASE в следните две форми:

• CASE стойност1

WHEN value2 THEN NULL

ELSE стойност1

• СЛУЧАЙ

КОГА стойност1 = стойност2 ТОГАВА NULL

ELSE стойност1

Функция COALESCE ( стойност1, стойност2, ...,стойност N) приема списък със стойности, които могат да бъдат определени или нулеви. Функцията връща конкретна стойност от списъка или NULL, ако всички стойности са недефинирани.

Тази функция е еквивалентна на следния израз CASE:

СЛУЧАЙ

КОГАТО стойност 1 НЕ Е NULL ТОГАВА стойност 1

КОГАТО стойност 2 НЕ Е NULL ТОГАВА стойност 2

. . .

КОГАТО стойността N НЕ Е НУЛА, ТОГАВА стойността N

ДРУГО НУЛИ

Например в таблицата Книги (Заглавие, Цена) колоната Цена е NULL, ако съответната книга не е налична. Следната заявка връща таблица, в която вместоНУЛА се показва текстът "Изчерпан":

ИЗБЕРЕТЕ име, КОАЛЕСЦИЯ (CAST (Цена КАТО CHAR (8)),

"Изчерпан") AS Цена

ОТ КНИГИ;

SQL урок 11. Обобщени функции, изчислени колони и изгледи

• Функцията AVG () връща средната стойност на колона.


• COUNT () Функцията връща броя на редовете в колона.


• MAX () Функцията връща най-много голямо значениев колоната.


• MIN () Функцията връща най-малката стойност в колона.


• Функция SUM () връща сумата от стойностите на колоните.

ИЗБЕРЕТЕ MIN (цена), MAX (цена), AVG (цена) ОТ цени;

Сега искаме да разберем колко ни донесе стоката от доставчика "Печатница" (id = 2). Не е толкова лесно да се състави такава молба. Нека помислим как да го съставим:

1. Първо, от таблицата Доставки (входящи) изберете идентификаторите (id_incoming) на тези доставки, които са извършени от доставчика на "Печатница" (id = 2):

2. Сега от таблицата Списание за доставки (magazine_incoming) трябва да изберете стоките (id_product) и техните количества (количество), които са били извършени в доставките, намерени в параграф 1. Тоест искането от параграф 1 става вложено:

3. Сега трябва да добавим към получената таблица цените за намерените продукти, които се съхраняват в таблицата с цените. Тоест трябва да обединим таблиците Magazine_incoming и Prices чрез колоната id_product:

4. В получената таблица явно липсва колоната Sum, т.е изчислена колона... Възможността за създаване на такива колони е предоставена в MySQL. За да направите това, просто трябва да посочите в заявката името на изчислената колона и какво трябва да изчисли. В нашия пример такава колона ще се нарича сума и тя ще изчисли произведението на колоните количество и цена. Новото име на колона е разделено с думата AS:

ИЗБЕРЕТЕ magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * price.price КАТО сума от списание_incoming, цени WHERE magazine_incoming.id_product = price.id_product И id_incoming = (ИЗБЕРЕТЕ id_incoming ОТ входящ WHERE 2 id

5. Чудесно, остава само да обобщим колоната за суми и накрая да разберем колко ни е донесъл стоката от доставчика "Печатница". Синтаксисът за използване на функцията SUM () е както следва:

SELECT SUM (име_на_колона) ОТ име_на_таблица;

Знаем името на колоната - suma, но нямаме името на таблицата, тъй като то е резултат от заявката. Какво да правя? За такива случаи MySQL предоставя Views. Изгледът е заявка за избор, на която е присвоено уникално име и може да се съхранява в база данни за по-късна употреба.

Синтаксисът за създаване на изглед е както следва:

CREATE VIEW view_name КАТО заявка;

Нека запазим нашата заявка като изглед с име report_vendor:

CREATE VIEW report_vendor КАТО ИЗБЕРЕТЕ magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * price.price КАТО suma FROM magazine_incoming, цени WHERE magazine_incoming.id_product = price.id_product AND id_incoming = (FROMECT iFROMECT id_incoming) ;

6. Сега можете да използвате последната функция SUM ():

ИЗБЕРЕТЕ СУМА (сума) ОТ доклад_доставчик;

Така постигнахме резултата, въпреки че за това трябваше да използваме вложени заявки, обединявания, изчислени колони и изгледи. Да, понякога трябва да мислиш, за да постигнеш резултата, без него никъде. Но засегнахме две много важни теми – изчислени колони и изгледи. Нека поговорим за тях по-подробно.

Изчислени полета (колони)

SELECT column_1_name, column_2_name, column_1_name * column_2_name AS computed_column_name FROM table_name;

Вторият нюанс е ключовата дума AS, използвахме я, за да зададем името на изчислената колона. Всъщност тази ключова дума се използва за задаване на псевдоними за всякакви колони. Защо е необходимо това? За по-кратък и по-четлив код. Например нашият изглед може да изглежда така:

CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity * B.price AS suma FROM magazine_incoming КАТО A, цени КАТО B КЪДЕ A.id_product = B.id_product И id_incoming = (ИЗБЕРЕТЕ id_incoming ОТ входящ WHERE id_vendor = 2);

Съгласете се, че това е много по-кратко и по-ясно.

Представителство

Имайте предвид обаче, че изгледите не са таблици, тоест те не съхраняват данни, а само ги извличат от други таблици. Следователно, първо, когато данните в таблиците се променят, резултатите от представянето също ще се променят. И второ, когато се направи заявка към изглед, се търсят необходимите данни, тоест производителността на СУБД намалява. Следователно с тях не трябва да се злоупотребява.

Този урок ще ви покаже как да използвате функцията SUM v SQL Server(Transact-SQL) със синтаксис и примери.

Описание

SQL Server (Transact-SQL) Функция SUMвръща общата стойност на израза.

Синтаксис

Синтаксисът за функцията SUM в SQL Server (Transact-SQL) е:

ИЛИ синтаксисът за функцията SUM при групиране на резултатите по една или повече колони е:

Параметри или аргументи

израз1, израз2, ... израз_n са изрази, които не са включени във функцията SUM и трябва да бъдат включени в клаузата GROUP BY в края на SQL израза.
aggregate_expression е колоната или изразът, който трябва да се обобщи.
таблици - таблиците, от които искате да получите записи. Трябва да има поне една таблица, посочена в клаузата FROM.
Условията WHERE не са задължителни. Това са условията, които трябва да бъдат изпълнени за избраните записи.

Приложение

Функцията SUM може да се използва в следните версии на 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

Пример за едно поле

Нека разгледаме някои примери за функция SUM на SQL Server, за да разберем как да използвате функцията SUM в SQL Server (Transact-SQL).

Например, можете да разберете как общата сума на всички продукти, чийто брой е по-голям от 10.

В този пример за функцията SUM ние задаваме израза SUM (количество) на псевдонима "Общо количество". При връщане на набор от резултати - "Общо количество" ще се покаже като име на полето.

Пример за използване на DISTINCT

Можете да използвате оператора DISTINCT във функцията SUM. Например даден по-долу SQL изявлениевръща общата заплата с уникални стойности на заплатата, където заплатата е по-малка от $29 000 на година.

Ако две заплати бяха 24 000 долара на година, само една от тези стойности щеше да се използва във функцията SUM.

Пример за използване на формула

Изразът, съдържащ се във функцията SUM, не трябва да бъде едно поле. Можете също да използвате формула. Например, можете да изчислите общата комисионна.

Transact-SQL

ИЗБЕРЕТЕ СУМА (продажби * 0,03) КАТО "Обща комисионна" ОТ поръчки;

Пример за използване на GROUP BY

В някои случаи ще трябва да използвате клаузата GROUP BY с функцията SUM.

Как мога да разбера броя на моделите компютри, произведени от конкретен доставчик? Как да определим средната цена за компютри, които имат същата спецификации? На тези и много други въпроси, свързани с някаква статистическа информация, може да се отговори с помощта обобщени (агрегирани) функции... Стандартът предвижда следните агрегатни функции:

Всички тези функции връщат една стойност. В този случай функциите БРОЙ, МИНи МАКСса приложими за всеки тип данни, докато СУМи AVGсе използват само за цифрови полета. Разлика между функцията БРОЯ (*)и БРОЯ (<имя поля>) е, че вторият не взема под внимание NULL стойности при изчисляване.

Пример. Намерете минималната и максималната цена за персонални компютри:

Пример. Намерете броя на наличните компютри от производител A:

Пример. Ако се интересуваме от броя на различните модели, произведени от производителя А, тогава заявката може да бъде формулирана по следния начин (използвайки факта, че в таблицата на продуктите всеки модел се записва веднъж):

Пример. Намерете броя на различните модели, налични от производителя A. Заявката е подобна на предишната, в която се изискваше да се определи общият брой модели, произведени от производителя A. Тук също трябва да намерите броя на различните модели в PC маса (т.е. налична за продажба).

За да се гарантира, че се използват само уникални стойности при получаване на статистически показатели, когато аргумент на агрегатните функцииможе да се използва DISTINCT параметър... Друг параметър ВСИЧКИе по подразбиране и предполага, че всички върнати стойности в колоната се броят. Оператор,

Ако трябва да получим броя на произведените модели компютри всекипроизводител, ще трябва да използвате Клауза GROUP BYсинтактично следване WHERE клаузи.

Клауза GROUP BY

Ако SELECT имаше колона с дата, тогава би било възможно да се изчислят тези показатели за всяка конкретна дата. За да направите това, трябва да добавите дата като колона за групиране и след това агрегатните функции ще бъдат изчислени за всяка комбинация от стойности (модел-дата).

Има няколко специфични правила за изпълнение на агрегатни функции:

• Ако в резултат на заявката не са получени линии(или повече от един ред за дадена група), тогава изходните данни за изчисляване на която и да е от агрегатните функции отсъстват. В този случай резултатът от изпълнението на функциите COUNT ще бъде нула, а резултатът от всички други функции ще бъде NULL.
• Аргумент агрегатна функция сама по себе си не може да съдържа агрегатни функции(функция от функция). Тези. в една заявка не можете, да речем, да получите максимума от средните стойности.
• Резултатът от изпълнението на функцията COUNT е цяло число(ЦЯЛО ЧИСЛО). Други агрегатни функции наследяват типовете данни на стойностите, които се обработват.
• Ако при изпълнение на функцията SUM се получи резултат, който надвишава максималната стойност на използвания тип данни, грешка.

Така че, ако искането не съдържа GROUP BY клаузи, тогава агрегатни функциивключен в SELECT клауза, се изпълняват на всички произтичащи линии на заявка. Ако искането съдържа Клауза GROUP BY, всеки набор от редове, който има същите стойности на колона или група колони, посочени в Клауза GROUP BY, представлява група и агрегатни функциисе изпълняват за всяка група поотделно.

клауза HAVING

Ако клауза WHEREтогава дефинира предикат за филтриране на редове ИМАТЕ офертаприлаган след групиранеза да дефинирате подобни предикатни филтриращи групи по стойности агрегатни функции... Тази клауза е необходима за тестване на стойностите, които се получават с помощта агрегатна функцияне от отделни редове на източника на запис, дефиниран в клауза ОТ, и от групи от такива линии... Следователно такава проверка не може да се съдържа в клауза WHERE.