Как сделать сумму двух столбцов в SQL и вывести новый столбец с результатами этих сумм
В этой таблице мне необходимо добавить новый столбик с результатами суммы двух столбцов Math и Rus для каждой строчки. Как это сделать?
Отслеживать
задан 26 апр 2020 в 12:04
voooooooojyay voooooooojyay
1 1 1 золотой знак 1 1 серебряный знак 1 1 бронзовый знак
Обычно все пишут сумму всего столбца. а мне надо для каждой строчки по отдельности
26 апр 2020 в 12:32
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
SELECT *, (Math + Rus) as summ FROM tabl; Если вы имеете ввиду столбик, который сам будет рассчитываться, вот пример использования виртуального столбца(был ответ где-то, но я его потерял):
create table t ( num1 int, num2 int, sum int as (num1 + num2)) ; insert into t (num1, num2) values (1, 2); select * from t; Выведет 1(num1), 2(num2), 3(sum)
Функция 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 не суммируется.)
Получение суммарных или статистических значений для всех строк в таблице (визуальные инструменты для баз данных)
Агрегатные функции позволяют вычислять сумму всех значений, содержащихся в таблице. Например, можно создать запрос наподобие того, что приведен ниже, для отображения общей стоимости всех книг, содержащихся в таблице titles :
SELECT SUM(price) FROM titles Создайте несколько агрегатов в одном запросе, используя агрегатные функции для одного или нескольких столбцов. Например, можно создать запрос, вычисляющий общую сумму по столбцу price и среднеарифметическое значение столбца discount .
В отдельном запросе для одного и того же столбца можно определять несколько статистических операций (подведение общей суммы, подсчет, усреднение). Например, следующий запрос определяет среднеарифметическое значение и общую сумму столбца price в таблице titles :
SELECT AVG(price), SUM(price) FROM titles Если добавить условия поиска, можно вычислять статистические значения на основе подмножества строк, удовлетворяющих заданному условию.
Можно также подсчитать, сколько всего в таблице содержится строк, или строк, удовлетворяющих определенному условию. Дополнительные сведения см. в разделе Подсчет строк в таблице (визуальные инструменты для баз данных).
Если в запросе указано статистическое выражение, то для всех строк в таблице отображается только само это статистическое значение. Например, при подсчете итогового значения столбца price в таблице titles названия, имена издателей и другие столбцы не отображаются.
! При создании подытогов (например, групп) можно выводить значения столбцов для каждой группы. Дополнительные сведения см. в разделе Группирование строк в результатах запроса (визуальные инструменты для баз данных).
Статистическая обработка значений по всем строкам
- Убедитесь, что таблица, в которой необходимо вычислить статистическую величину, присутствует на панели диаграмм
- Щелкните правой кнопкой мыши фон панели диаграммы, а затем в контекстном меню выберите пункт Группировать . Конструктор запросов и представлений добавляет столбец Группировать в сетку на панели критериев.
- Добавьте на панель критериев столбец, по которому необходимо вычислить статистическую величину. Убедитесь, что столбец помечен для вывода. Конструктор запросов и представлений автоматически назначает суммируемому столбцу псевдоним, который можно заменить более понятным. Дополнительные сведения см. в разделе Создание псевдонимов столбцов (визуальные инструменты для баз данных).
- В столбце сетки Группировать выберите нужную агрегатную функцию, например Sum, Avg, Min, Max, Count. Если необходимо вычислить статистические значения только для уникальных строк результирующего набора, выберите агрегатную функцию с параметром DISTINCT, например Min Distinct. Не выбирайте параметры Группировать, Выражениеили Где, так как они не применяются при статистической обработке всех строк. Конструктор запросов и представлений заменяет указанной агрегатной функцией имя столбца в инструкции, представленной на панели SQL . Например, инструкция SQL может иметь такой вид:
SELECT SUM(price) FROM titles При выполнении запроса на панели результатов отображаются указанные статистические выражения.
Конструктор запросов и представлений обслуживает агрегатные функции в инструкции, отображаемой на панели SQL до тех пор, пока режим «Группировать по» явно не будет выключен. Поэтому при изменении типа запроса или изменении состава таблиц и возвращающих табличное значение объектов, присутствующих на панелях диаграмм, конечный запрос может содержать недопустимые агрегатные функции.
Три способа рассчитать накопленную сумму в SQL
Расчет накопленной (или кумулятивной, что то же самое) суммы SQL — это очень распространенный запрос, который часто используют в анализе финансов, динамики прибыли и прочих показателей компании. В сегодняшней статье вы узнаете, что такое накопленная сумма и как можно написать SQL-запрос для ее вычисления.
Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге. Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.
Что у нас есть?
| Date | Revenue |
| 10.11.2021 | 1200 |
| 11.11.2021 | 1600 |
| 12.11.2021 | 800 |
| 13.11.2021 | 3000 |
Что мы хотим найти?
| Date | Revenue | Cumulative Revenue |
| 10.11.2021 | 1200 | 1200 ↓ |
| 11.11.2021 | 1600 | 2800↓ |
| 12.11.2021 | 800 | 3600 ↓ |
| 13.11.2021 | 3000 | 6600 |
На графике две этих переменных выглядят следующим образом:
Итак, без лишних слов, давайте приступать к решению задачи.
Способ 1 — Идеальный — Используем оконные функции
Итак, если в базе данных можно пользоваться оконными функциями, то жизнь хороша и прекрасна. С их помощью можно написать простой запрос, который будет суммировать значения из столбца revenue по мере увеличения даты и сразу вернет нам таблицу с кумулятивной суммой в столбце, который мы назвали total.
SELECT date, revenue, SUM(revenue) OVER (ORDER BY date asc) as total FROM leftjoin.daily_sales_sample ORDER BY date;Способ 2 — Хитрый — Решение без оконных функций
Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?
Чтобы провернуть все то же самое без оконных функций, нужно использовать INNER JOIN для присоединения таблицы к себе самой. Так, к каждой строке таблицы мы присоединяем строки, которые соответствуют всем предыдущим датам до текущей даты включительно. В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:
SELECT * FROM leftjoin.daily_sales_sample ds1 INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date ORDER BY ds1.date, ds2.date;А его результат:
| Date 1 | Revenue 1 | Date 2 | Revenue 2 |
| 10.11.2021 | 1200 | 10.11.2021 | 1200 |
| 11.11.2021 | 1600 | 10.11.2021 | 1200 |
| 11.11.2021 | 1600 | 11.11.2021 | 1600 |
| 12.11.2021 | 800 | 10.11.2021 | 1200 |
| 12.11.2021 | 800 | 11.11.2021 | 1600 |
| 12.11.2021 | 800 | 12.11.2021 | 800 |
| 13.11.2021 | 300 | 10.11.2021 | 1200 |
| 13.11.2021 | 300 | 11.11.2021 | 1600 |
| 13.11.2021 | 300 | 12.11.2021 | 800 |
| 13.11.2021 | 300 | 13.11.2021 | 300 |
А затем, нужно просуммировать прибыли, группируя их по каждой дате. Если собрать все в единый запрос, то он будет выглядеть вот так:
SELECT ds1.date, ds1.revenue, SUM(ds2.revenue) as total FROM leftjoin.daily_sales_sample ds1 INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date GROUP BY ds1.date, ds1.revenue ORDER BY ds1.date;Способ 3 — Специфический — Решение с помощью массивов в ClickHouse
Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.
SELECT groupArray(date) dates, groupArray(revenue) as revs, groupArrayMovingSum(revenue) AS total FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample ORDER BY date)Спасибо Дмитрию Титову из Altinity за комментарий про сортировку в подзапросе
Так, мы получим три массива значений:
| dates | revs | total |
| [’10.11.2021’,’11.11.2021’,’12.11.2021’,’13.11.2021’] | [1200, 1600, 800, 300] | [1200, 2800, 3600, 3900] |
Но три массива, которые записаны в ячейки — это не то, что мы хотим получить, хотя значения этих массивов уже абсолютно соответствуют искомому результату. Теперь массивы нужно привести обратно к табличному виду с помощью функции ARRAY JOIN.
SELECT dates, revs, total FROM (SELECT groupArray(date) dates, groupArray(revenue) as revs, groupArrayMovingSum(revenue) AS total FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample ORDER BY date)) as t ARRAY JOIN dates, revs, total;Бонус — Оконные функции в Clickhouse
Если вам не хочется иметь дело с массивами, что иногда и правда бывает затратно по времени, то есть еще один вариант решения задачи. Можно использовать оконные функции, например функцию runningAccumulate(), которая суммирует значения всех ячеек с первой до текущей.
SELECT date, runningAccumulate(revenue) FROM ( SELECT date, sumState(revenue) AS revenue FROM leftjoin.daily_sales_sample GROUP BY date ORDER BY date ASC ) ORDER BY dateЕсли вы столкнетесь с необходимостью рассчитать кумулятивную сумму в SQL, то теперь вы сможете решить эту задачу, в какой бы системе управления баз данных ни была организована работа 🙂