SQL — Урок 11. Итоговые функции, вычисляемые столбцы и представления
Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик «Дом печати» (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:
1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком «Дом печати» (id=2):
SELECT id_incoming FROM incoming WHERE id_vendor=2;
2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:
SELECT id_product, quantity FROM magazine_incoming WHERE id_incoming=(SELECT id_incoming FROM incoming WHERE id_vendor=2);
3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:
SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
4. В получившейся таблице явно не хватает столбца Сумма, то есть вычисляемого столбца . Возможность создания таких столбцов предусмотрена в MySQL. Для этого надо лишь указать в запросе имя вычисляемого столбца и что он должен вычислять. В нашем примере такой столбец будет называться summa, а вычислять он будет произведение столбцов quantity и price. Название нового столбца отделяется словом 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 WHERE id_vendor=2);
5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик «Дом печати». Синтаксис для использования функции SUM() следущий:
SELECT SUM(имя_столбца) FROM имя_таблицы;
Имя столбца нам известно — summa, а вот имени таблицы у нас нет, так как она является результатом запроса. Что же делать? Для таких случаев в MySQL существуют Представления . Представление — это запрос на выборку, которому присваивается уникальное имя и который можно сохранять в базе данных, для последующего использования.
Синтаксис создания представления следующий:
CREATE VIEW имя_представления AS запрос;
Давайте сохраним наш запрос, как представление с именем 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= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
6. Вот теперь можно использовать итоговую функцию SUM() :
SELECT SUM(summa) FROM report_vendor;
Вот мы и достигли результата, правда для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. Да, иногда для получения результата приходится подумать, без этого никуда. Зато мы коснулись двух очень важных тем — вычисляемые столбцы и представления. Давайте поговорим о них поподробнее.
Вычисляемые поля (столбцы)
На примере мы рассмотрели сегодня математическое вычисляемое поле. Здесь хотелось бы добавить, что использовать можно не только операцию умножения (*), но и вычитание (-), и сложение (+), и деление (/). Синтаксис следующий:
SELECT имя_столбца_1, имя_столбца_2, имя_столбца_1*имя_столбца_2 AS имя_вычисляемого_столбца FROM имя_таблицы;
Второй нюанс — ключевое слово AS, мы его использовали для задания имени вычисляемого столбца. На самом деле с помощью этого ключевого слова задаются псевдонимы для любых столбцов. Зачем это нужно? Для сокращения и читаемости кода. Например, наше представление могло бы выглядеть так:
CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, prices AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
Согласитесь, что так гораздо короче и понятнее.
Представления
Синтаксис создания представлений мы уже рассматривали. После создания представлений, их можно использовать так же, как таблицы. То есть выполнять запросы к ним, фильтровать и сортировать данные, объединять одни представления с другими. С одной стороны это очень удобный способ хранения частоприменяемых сложных запросов (как в нашем примере).
Но следует помнить, что представления — это не таблицы, то есть они не хранят данные, а лишь извлекают их из других таблиц. Отсюда, во-первых, при изменении данных в таблицах, результаты представления так же будут меняться. А во-вторых, при запросе к представлению происходит поиск необходимых данных, то есть производительность СУБД снижается. Поэтому злоупотреблять ими не стоит.
Онлайн-курс. Освойте востребованную профессию с зарплатой от 70 000 руб в месяц!
Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.
Функция SQL SUM()
Оператор SQL SUM() — функция, возвращающая сумму значений столбца таблицы. Используется только для числовых столбцов.
Функция SQL SUM() имеет следующий синтаксис:
SUM ( [ALL | DISTINCT] expression )
Параметр ALL — является параметром по умолчанию. Считается сумма всех строк.
При указании параметра DISTINCT — происходит подсчет только уникальных значений.
Примеры оператора SQL SUM: Имеется следующая таблица Universities :
| ID | UniversityName | Students | Faculties | Professores | Location | Site |
| 1 | Perm State National Research University | 12400 | 12 | 1229 | Perm | psu.ru |
| 2 | Saint Petersburg State University | 21300 | 24 | 13126 | Saint-Petersburg | spbu.ru |
| 3 | Novosibirsk State University | 7200 | 13 | 1527 | Novosibirsk | nsu.ru |
| 4 | Moscow State University | 35100 | 39 | 14358 | Moscow | msu.ru |
| 5 | Higher School of Economics | 20335 | 12 | 1615 | Moscow | hse.ru |
| 6 | Ural Federal University | 57000 | 19 | 5640 | Yekaterinburg | urfu.ru |
| 7 | National Research Nuclear University | 8600 | 10 | 936 | Moscow | mephi.ru |
Пример 1. Используя оператор SQL SUM найти количество студентов (Students) всех университетов в таблице:
SELECT SUM(Students) FROM Universities
Пример 2. Используя оператор SQL SUM найти количество преподавателей московских университетов:
SELECT SUM(Professores) FROM Universities WHERE Location = 'Moscow'
Пример 3. Используя оператор SQL SUM произвести подсчет уникальных значений столбца Faculties:
SELECT SUM(DISTINCT Faculties) FROM Universities
Ответ: 117 (примечание: в столбце Faculties присутствует 2 одинаковых записи, под ID 1 и 5. Значение столбца под ID 5 не суммируется.)
SQL SELECT Сумма строк
Вывести список проектов с указанием сумм, выплачиваемых их участникам (зарплаты и надбавки).
SELECT PROJECT.Project, SUM(Salary), SUM(Bonus) FROM PROJECT INNER JOIN PROJECT_EMP ON PROJECT_EMP.ID_Project=PROJECT.ID_Project INNER JOIN EMP ON PROJECT_EMP.ID_Emp=EMP.ID_Emp LEFT JOIN BONUS ON BONUS.ID_Emp=EMP.ID_Emp GROUP BY PROJECT.Project
Но нужно, чтобы вместо двух последних столбиков был один с их суммой
Отслеживать
задан 7 апр 2015 в 23:57
947 5 5 золотых знаков 19 19 серебряных знаков 31 31 бронзовый знак
Если вам дан исчерпывающий ответ, отметьте его как верный (галка напротив выбранного ответа).
13 мар 2016 в 22:51
5 ответов 5
Сортировка: Сброс на вариант по умолчанию
SELECT PROJECT.Project, SUM(Salary) + ISNULL( SUM(Bonus), 0) FROM PROJECT INNER JOIN PROJECT_EMP ON PROJECT_EMP.ID_Project=PROJECT.ID_Project INNER JOIN EMP ON PROJECT_EMP.ID_Emp=EMP.ID_Emp LEFT JOIN BONUS ON BONUS.ID_Emp=EMP.ID_Emp GROUP BY PROJECT.Project 
Отслеживать
ответ дан 8 апр 2015 в 9:03
947 5 5 золотых знаков 19 19 серебряных знаков 31 31 бронзовый знак
используйте SUM(Salary) + SUM(Bonus)
Отслеживать
ответ дан 8 апр 2015 в 0:42
599 4 4 серебряных знака 5 5 бронзовых знаков
не подходит. последняя строчка получается НУЛЛ. НУЛЛ + число = НУЛЛ
8 апр 2015 в 8:33
Осталось только добавить ‘+‘ и название поля Sum для наглядности
SELECT PROJECT.Project, SUM(Salary) **+** SUM(Bonus) **AS Sum** FROM PROJECT INNER JOIN PROJECT_EMP ON PROJECT_EMP.ID_Project=PROJECT.ID_Project INNER JOIN EMP ON PROJECT_EMP.ID_Emp=EMP.ID_Emp LEFT JOIN BONUS ON BONUS.ID_Emp=EMP.ID_Emp GROUP BY PROJECT.Project Отслеживать
ответ дан 8 апр 2015 в 0:56
1,865 1 1 золотой знак 17 17 серебряных знаков 25 25 бронзовых знаков
не подходит. последняя строчка получается НУЛЛ. НУЛЛ + число = НУЛЛ
8 апр 2015 в 8:33
В следующий раз уточняйте какую БД используете, в тегах есть и MySQL и MSSql, это далеко не одно и тоже
8 апр 2015 в 10:24
SUM(salary) + IFNULL(SUM(bonus), 0) Если сумма SUM(salary) тоже может приобретать значение NULL , то и её аналогично обернуть в вызов IFNULL() . Это в MySQL, в MS SQL же аналогом этой функции является ISNULL() .
Отслеживать
ответ дан 8 апр 2015 в 8:38
671 6 6 серебряных знаков 18 18 бронзовых знаков
пробовал так — ошибку пишет. не нравится ему что агрегатная функция внутри ifnull
8 апр 2015 в 8:49
вместо IFNULL нужно использовать ISNULL. MS SQL почему-то не работает корректно с ним
8 апр 2015 в 8:58
@neko69, я на MySQL только проверял. Посмотрел сейчас, действительно в MS SQL эта функция называется ISNULL. Дополню ответ.
8 апр 2015 в 10:35
В MS SQL надо SUM(ISNULL(Salary, 0)) + SUM(ISNULL(Bonus, 0)).
Вообще, если поле может содержать NULL, то в агрегатных функциях нужно внутри функции проврять на NULL.
Если прменять как ISNULL(SUM(Bonus), 0), то можно получить неверный результат в следующем случае: если только в некоторых строках группируемых данных в поле Bonus есть NULL, а у остальных реальное значение, то эти значения не будут учитываться
(к примеру по проекту А:
* Сотрудник1 — Бонус=10
* Сотрудник2 — Бонус=5
* Сотрудник3 — Бонус=NULL.
При ISNULL(SUM(Bonus), 0) результат будет 0,
При SUM(ISNULL(Bonus, 0)) результат будет 15)
Отслеживать
ответ дан 9 апр 2015 в 7:39
Vasil Baymurzin Vasil Baymurzin
1,150 7 7 серебряных знаков 14 14 бронзовых знаков
- mysql
- sql
- sql-server
-
Важное на Мете
Похожие
Подписаться на ленту
Лента вопроса
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
Дизайн сайта / логотип © 2023 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2023.10.27.43697
Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.
Как посчитать сумму SQL запросом?
Нужно вставить в таблицу 1 стоимость товара, которая равна сумме стоимости его запчастей.
товар id 7 цена=300+400+500
товар id 6 цена=200+300
Для каждого id из таблицы 2 посчитать его стоимость и обновить в таблице 1.
Можно ли это сделать SQL запросом без обработки данных через php и если да, то как?
Таблица 1 (данные и по товарам и по запчастям)
id price
1 100
2 200
3 300
4 400
5 500
6 0 — после обработки 500
7 0 — после обработки 1200
Таблица 2 (Id товара и Id запчастей к нему)
id id_zapchast
6 2
6 3
7 3
7 4
7 5
- Вопрос задан более трёх лет назад
- 14091 просмотр
Комментировать
Решения вопроса 1
Senior Developer
Как то так:
Select t1.id, sum(t2.price) From таблица2 as t1 Inner join таблица1 as t2 on t2.id = t1.id_zapchast group by t1.idОтвет написан более трёх лет назад
Комментировать
Нравится 3 Комментировать