Sql сумма по столбцу: Урок 11. Итоговые функции, вычисляемые столбцы и представления

sql запрос к столбцу сумма суммы

в моей таблице есть данные, как показано ниже…

Теперь в моей квитанции ниже table…but есть 18 строк, которые не совпадают.

но мне нужно отобразить сумму суммы уникальной квитанции не только.

plz помогите мне сделать запрос sql.

SELECT SUM(Amount) FROM `receipt_entry` group by receipt_no,amount


receipt_no              amount
    10                    100
    10                    100
    10                    100
    10                    100
    10                    100
    10                    100

    11                    100
    11                    100
    11                    100
    11                    100
    11                    100
    11                    100

    12                    100
    12                    100
    12                    100
    12                    100
    12                    100
    12                    100

  SUM of Amount        = 300

Для вышеописанного экс-мне нужно суммировать сумму, как показано ниже =100+100+100= 300

php

mysql

Поделиться

Источник

varsha bajpai    

23 марта 2015 в 12:54

2 ответа

• Получение суммы столбца таблицы SQL до тех пор, пока сумма не достигнет 5000

  Я работаю над запросом sql для таблицы с двумя столбцами Amount и Date , который должен возвращать сумму значений столбца Amount до достижения 5000 , а также возвращать значение в столбце Date , при котором Sum(Amount) достигает 5000 , отсортированное по Date Например, у меня есть следующие данные…

• SQL условие суммы запроса

  У меня есть три таблицы, а именно class_record, class_violation, violation. Таблица class_record содержит следующие столбцы и данные: Class Violation CR No. | Class ID 000-000 | A30-000 000-001 | A30-000 Таблица class_violation содержит следующие столбцы и данные: Class Violation CR No. |…

1

Используйте предложение distinct перед данными sum .

Делай вот так:

$query = "SELECT sum(amount) AS total_amount, receipt_no 
          FROM receipt_entry 
          GROUP BY receipt_no"

Вы можете получить более подробную информацию о DISTINCT отсюда

Дайте мне знать для получения дополнительной помощи !!

Поделиться

Anand Solanki    

23 марта 2015 в 12:57

0

Можете ли вы проверить этот запрос:

SELECT SUM (A.AmountTotal) FROM  (
    SELECT SUM(DISTINCT amount) AS AmountTotal, receipt_no 
    FROM receipt_entry
    GROUP BY receipt_no ) A

Этот блок возвращает результат:

SELECT SUM(DISTINCT amount) AS AmountTotal, receipt_no 
FROM receipt_entry
GROUP BY receipt_no

AmountTotal     receipt_no
-----------     ----------
100             10
100             11
100             12

Снова складываем сумму блока, получаем результат.

UPDATE:

SELECT SUM(`AmountTotal`) FROM  (
    SELECT SUM(DISTINCT `amount`) AS AmountTotal, `receipt_no` 
    FROM `receipt_entry`
    GROUP BY `receipt_no`) AS T1;

Можете ли вы проверить этот запрос?

Рабочий пример этого сценария: SQL Fiddle

Поделиться

Arulkumar    

23 марта 2015 в 13:19

Похожие вопросы:

Есть ли какой-нибудь способ дать столбцу, к какой таблице он принадлежит, используя запрос SQL?

Есть ли какой-нибудь способ дать столбцу, к какой таблице он принадлежит, используя запрос SQL?

SQL запрос к группе на основе суммы

У меня есть простая таблица со значениями, которые я хочу разбить на отдельные группы на основе суммы этих значений (до определенной предельной суммы групп). например, представьте себе таблицу,…

SQL запрос на поиск суммы столбца

У меня есть таблица, где я должен получить вывод следующим образом: ID — amount — TotalAmount 1 — 400 — 400 2 — 600 — 1000 3 — 400 — 1400 Таблица состоит из двух столбцов: ID & сумма….

Получение суммы столбца таблицы SQL до тех пор, пока сумма не достигнет 5000

Я работаю над запросом sql для таблицы с двумя столбцами Amount и Date , который должен возвращать сумму значений столбца Amount до достижения 5000 , а также возвращать значение в столбце Date , при…

SQL условие суммы запроса

У меня есть три таблицы, а именно class_record, class_violation, violation. Таблица class_record содержит следующие столбцы и данные: Class Violation CR No. | Class ID 000-000 | A30-000 000-001 |…

codeigniter запрос суммы для одного столбца

У меня есть запрос, который я пытаюсь вернуть результат суммы столбцов, где значение превышает 10 000. Я попытался добавить sum(value) в качестве total к запросу, но это не работает. Мой код таков:…

sql вложенный запрос суммы

У меня есть одна таблица, в которой мне нужно создать три суммы на основе месяца по счету-фактуре. Суммы рассчитываются с использованием первых трех букв поля ‘invoice_number’, а затем добавляются…

elasticsearch-сумма общей суммы больше некоторой суммы с использованием агрегации

Я пытаюсь получить записи, где моя общая сумма агрегации больше, чем какая-то сумма, скажем, 1000. Ниже приведены образцы документов. [ { _index: orders_stage, _type: order, _id:…

Ограничена общая сумма в SQL

Как я могу использовать SQL для вычисления кумулятивной суммы по столбцу, чтобы кумулятивная сумма всегда оставалась в пределах верхних/нижних границ? Пример с нижней границей -2 и верхней границей…

SQL суммы с пунктами Where

У меня есть таблица hours, в которой я пытаюсь создать запрос SQL, чтобы суммировать некоторые суммы в зависимости от пары переменных. В моем столе есть такие HOURS, Местоположение, Пользователь,…

Получение суммарных или статистических значений для всех строк в таблице — Visual Database Tools

• 
  01/19/2017
• Чтение занимает 3 мин

В этой статье

Применимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) База данных SQL AzureAzure SQL DatabaseБаза данных SQL AzureAzure SQL Database Управляемый экземпляр SQL AzureAzure SQL Managed InstanceУправляемый экземпляр SQL AzureAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics Параллельное хранилище данныхParallel Data WarehouseПараллельное хранилище данныхParallel Data WarehouseПрименимо к:Applies to: SQL ServerSQL Server (все поддерживаемые версии) SQL ServerSQL Server (all supported versions) База данных SQL AzureAzure SQL DatabaseБаза данных SQL AzureAzure SQL Database Управляемый экземпляр SQL AzureAzure SQL Managed InstanceУправляемый экземпляр SQL AzureAzure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics Параллельное хранилище данныхParallel Data WarehouseПараллельное хранилище данныхParallel Data Warehouse

Агрегатная функцияAggregate function

Агрегатные функции позволяют вычислять сумму всех значений, содержащихся в таблице.Using an aggregate function, you can create a summary for all the values in a table. Например, можно создать запрос наподобие того, что приведен ниже, для отображения общей стоимости всех книг, содержащихся в таблице titles :For example, you can create a query such as the following to display the total price for all books in the titles table:

SELECT SUM(price)  
FROM titles  

Создайте несколько агрегатов в одном запросе, используя агрегатные функции для одного или нескольких столбцов.Create multiple aggregations in the same query by using aggregate functions with more than one column. Например, можно создать запрос, вычисляющий общую сумму по столбцу price и среднеарифметическое значение столбца discount .For example, you can create a query that calculates the total of the price column and the average of the discount column.

В отдельном запросе для одного и того же столбца можно определять несколько статистических операций (подведение общей суммы, подсчет, усреднение).You can aggregate the same column in different ways (such as totaling, counting, and averaging) in the same query. Например, следующий запрос определяет среднеарифметическое значение и общую сумму столбца price в таблице titles :For example, the following query averages and summarizes the price column from the titles table:

SELECT AVG(price), SUM(price)  
FROM titles  

Если добавить условия поиска, можно вычислять статистические значения на основе подмножества строк, удовлетворяющих заданному условию.If you add a search condition, you can aggregate the subset of rows that meet that condition.

Примечание.Note! Можно также подсчитать, сколько всего в таблице содержится строк, или строк, удовлетворяющих определенному условию.You can also count all the rows in the table or the ones that meet a specific condition. Дополнительные сведения см. в разделе Подсчет строк в таблице (визуальные инструменты для баз данных).For details, see Count Rows in a Table (Visual Database Tools).

Если в запросе указано статистическое выражение, то для всех строк в таблице отображается только само это статистическое значение.When you create a single aggregation value for all rows in a table, you display only the aggregate values themselves. Например, при подсчете итогового значения столбца price в таблице titles названия, имена издателей и другие столбцы не отображаются.For example, if you are totaling the value of the price column of the titles table, you would not also display individual titles, publisher names, and so on.

!! При создании подытогов (например, групп) можно выводить значения столбцов для каждой группы.If you are subtotaling — that is, creating groups — you can display column values for each group. Дополнительные сведения см. в разделе Группирование строк в результатах запроса (визуальные инструменты для баз данных).For details, see Group Rows in Query Results (Visual Database Tools).

Статистическая обработка значений по всем строкамAggregate values for all rows

1. Убедитесь, что таблица, в которой необходимо вычислить статистическую величину, присутствует на панели диаграммBe sure the table you want to aggregate is already present in the Diagram pane.

2. Щелкните правой кнопкой мыши фон панели диаграммы, а затем в контекстном меню выберите пункт Группировать .Right-click the background of the Diagram pane, then choose Group By from the shortcut menu. Конструктор запросов и представлений добавляет столбец Группировать в сетку на панели критериев.The Query and View Designer adds a Group By column to the grid in the Criteria pane.

3. Добавьте на панель критериев столбец, по которому необходимо вычислить статистическую величину.Add the column you want to aggregate to the Criteria pane. Убедитесь, что столбец помечен для вывода.Be sure that the column is marked for output.

   Конструктор запросов и представлений автоматически назначает суммируемому столбцу псевдоним,The Query and View Designer automatically assigns a column alias to the column you are summarizing. который можно заменить более понятным.You can replace this alias with a more meaningful one. Дополнительные сведения см. в разделе Создание псевдонимов столбцов (визуальные инструменты для баз данных).For details, see Create Column Aliases (Visual Database Tools).

4. В столбце сетки Группировать выберите нужную агрегатную функцию, например Sum, Avg, Min, Max, Count.In the Group By grid column, select the appropriate aggregate function, such as: Sum, Avg, Min, Max, Count. Если необходимо вычислить статистические значения только для уникальных строк результирующего набора, выберите агрегатную функцию с параметром DISTINCT, например Min Distinct.If you want to aggregate only unique rows in the result set, choose an aggregate function with the DISTINCT options, such as Min Distinct. Не выбирайте параметры Группировать, Выражение или Где, так как они не применяются при статистической обработке всех строк.Do not choose Group By, Expression, or Where, because those options do not apply when you are aggregating all rows.

   Конструктор запросов и представлений заменяет указанной агрегатной функцией имя столбца в инструкции, представленной на панели SQL .The Query and View Designer replaces the column name in the statement in the SQL pane with the aggregate function that you specify. Например, инструкция SQL может иметь такой вид:For example, the SQL statement might look like this:

   SELECT SUM(price)  
   FROM titles  
   

5. Если необходимо создать в запросе несколько статистических выражений, повторите шаги 3 и 4.If you want to create more than one aggregation in the query, repeat steps 3 and 4.

   Если в список выводов запроса или в список сортировки добавляется другой столбец, конструктор запросов и представлений автоматически заполняет термин Группировать в столбце сетки Группировать .When you add another column to the query output list or order by list, the Query and View Designer automatically fills the term Group By into the Group By column of the grid. Выберите соответствующую агрегатную функцию.Select the appropriate aggregate function.

6. Если необходимо, добавьте условия поиска для задания подмножества строк, для которых вычисляется сумма.Add search conditions, if any, to specify the subset of rows you want to summarize.

При выполнении запроса на панели результатов отображаются указанные статистические выражения.When you execute the query, the Results pane displays the aggregations that you specified.

Примечание

Конструктор запросов и представлений обслуживает агрегатные функции в инструкции, отображаемой на панели SQL до тех пор, пока режим «Группировать по» явно не будет выключен.The Query and View Designer maintains aggregate functions as part of the SQL statement in the SQL pane until you explicitly turn off Group By mode. Поэтому при изменении типа запроса или изменении состава таблиц и возвращающих табличное значение объектов, присутствующих на панелях диаграмм, конечный запрос может содержать недопустимые агрегатные функции.Therefore, if you modify your query by changing its type or by changing which tables or table-valued objects are present in the Diagram pane, the resulting query might include invalid aggregate functions.

См. также:See Also

Результаты запросов сортировки и группирования (визуальные инструменты для баз данных)Sort and Group Query Results (Visual Database Tools)
Резюмирование результатов запросов (визуальные инструменты для баз данных)Summarize Query Results (Visual Database Tools)

Функция SQL SUM: синтаксис, синтаксис

Оператор SQL SUM() — функция, возвращающая сумму значений столбца таблицы. Используется только для числовых столбцов.

Функция SQL SUM() имеет следующий синтаксис:

SUM ( [ALL | DISTINCT] expression )

Параметр ALL — является параметром по умолчанию. Считается сумма всех строк.

При указании параметра DISTINCT — происходит подсчет только уникальных значений.

Примеры оператора SQL SUM: Имеется следующая таблица Universities:

Пример 1. Используя оператор SQL SUM найти количество студентов (Students) всех университетов в таблице:

SELECT SUM(Students)
FROM Universities

Ответ: 161935

Пример 2. Используя оператор SQL SUM найти количество преподавателей московских университетов:

SELECT SUM(Professores)
FROM Universities
WHERE Location = 'Moscow'

Ответ: 16909

Пример 3. Используя оператор SQL SUM произвести подсчет уникальных значений столбца Faculties:

SELECT SUM(DISTINCT Faculties)
FROM Universities

Ответ: 117 (примечание: в столбце Faculties присутствует 2 одинаковых записи, под ID 1 и 5. Значение столбца под ID 5 не суммируется.)

sql — Сумма двух столбцов вместе с отдельным оператором выбора

Я работаю над SQL task и не могу понять, как получить sum of two columns из той же таблицы при отображении информации из другой таблицы.

Я попробовал несколько вещей и потратил около двух часов, пытаясь понять это.

У меня есть две таблицы: Employees and Fuel. Я отобразил всю информацию о сотруднике. Первое заявление SQL, которое я должен был сделать:

SELECT firstname, lastname, title, registrationyear, make, model FROM Employees ORDER BY make;

Мой Employees table имеет следующие столбцы: firstname, lastname, employeeid, make, model, registrationyear, title

Мой Fuel table имеет следующие столбцы: currentprice, fueltype, fuelcost, mileage, mileagecount, fuelamount, employeeid, date

Мои инструкции гласят: «Список, который показывает, какие машины используют сотрудники в настоящее время (первое заявление SQL, которое я сделал, так что это СДЕЛАНО!) Как и в приведенном выше отчете, а также общее количество километров, которые проехали сотрудники, и общая стоимость топлива. « (это задача, для которой я пытаюсь сделать заявление)

Я попытался использовать LIKE, UNION, UNION ALL и т. Д., И лучшее, что я смог сделать, — это перечислить информацию о сотруднике и итоговые данные в верхнем углу информации, а не в двух отдельных столбцах вместе с другими данными в запрос .

Я действительно застрял здесь. Может ли кто-нибудь, пожалуйста, помогите мне?

0

Trevor Gutierrez

11 Апр 2017 в 11:33

2 ответа

Лучший ответ

Эта вторая задача намного сложнее, чем первая.

Прежде всего, объединение в одну строку столбцов из двух или более таблиц — это то, для чего join, поэтому вам нужно объединить две таблицы на основе employeeid. Это вернет вам такую таблицу

employeeid | other emp fields | fuel date  | other fueld fields
1          | ...              | 01/01/2017 | ...
1          | ...              | 01/02/2017 | ...
2          | ...              | 01/01/2017 | ...
2          | ...              | 02/01/2017 | ...
2          | ...              | 04/03/2017 | ...

Отсюда вы хотите, чтобы данные из каждого employee объединялись с суммой строк из fuel, связанных с этим employee, и именно для этого group by.

При использовании group by вы определяете набор столбцов, который определяет критерии группировки; все остальное в операторе select должно быть каким-то образом сгруппировано (в вашем случае с sum), чтобы столбцы в group by оставались уникальными.

Ваш последний запрос будет выглядеть так

select  t1.firstname, t1.lastname, t1.title, t1.registrationyear, t1.make, t1.model,
        sum(t2.mileage) as total_milege,
        sum(t2.fuelcost * t2.fuelamount) as total_fuel_cost
from    Employees t1
join    Fuel t2
on      t1.employeeid = t2.employeeid
group by t1.firstname, t1.lastname, t1.title, t1.registrationyear, t1.make, t1.model

Примечание: я не знаю разницы между mileage и mileagecount, поэтому часть моего запроса, включающая эти поля, может потребовать некоторой настройки.

0

Stefano Zanini
11 Апр 2017 в 08:52

Вы можете использовать Inner join и Group By, как указано ниже. Дайте мне знать, если вы имеете в виду что-то еще.

SELECT A.firstname, A.lastname, A.title, A.registrationyear, A.make, A.model,
SUM(B.Column_Having_Kilometer_Driven_Value)
FROM
Employee A
INNER JOIN Fuel B ON A.EmployeeID = B.EmployeeID
Group By A.EmployeeID, A.firstname, A.lastname, A.title, A.registrationyear, A.make, A.model

0

Brijesh
11 Апр 2017 в 08:43

Агрегирование и группировка. Курс «Введение в реляционные базы данных»

Как быть, если надо посчитать общее количество строк таблицы или найти запись, содержащую максимальное значение, или посчитать сумму значений столбца? Для этих целей в языке SQL предусмотрены различные функции агрегирования данных. Наиболее используемые – count(), sum(), avr(), min(), max(). Используют их совместно с оператором SELECT.

Вывод количества столбцов таблицы:

sqlite> SELECT count() FROM pages;

Поиск максимального ID:

sqlite> SELECT max(_id) FROM pages;

Количество различных вариантов значения столбца:

sqlite> SELECT count(DISTINCT theme) 
   ...> FROM pages;

На скрине команда с DISTINCT возвращает 3 потому, что у нас встречается три разных значения в столбце theme – это значения 1, 2 и 3. Тема 1 встречается у двух записей, но благодаря агрегированию они учитываются как одна.

Вообще DISTINCT перед именем столбца выводит его различающиеся значения. Например, мы хотим узнать, какие темы используются в таблице pages:

sqlite> SELECT DISTINCT theme FROM pages;
1
2
3

Если в функцию count() передается просто имя столбца, например count(theme), то она возвращает количество записей с не NULL значениями. Если в указанном столбце нигде не встречается NULL, то результат будет совпадать с общим количеством записей.

Перед агрегированием можно выполнить фильтрацию. Данная команда посчитает количество страниц определенной темы:

sqlite> SELECT count() FROM pages 
   ...> WHERE theme = 1;
2

Обратим внимание, в SQL сначала выполняется фильтрация, то есть оператор WHERE. И только после этого агрегирование, то есть функция count(). Таким образом, из всей таблицы сначала фильтруются записи с темой 1. После этого считается их количество.

Если было бы наоборот, то приведенная выше команда не сработала, потому что функция count() вернула бы число-количество строк таблицы, и фильтровать из него было бы уже нечего.

В SQL кроме функций агрегирования есть оператор GROUP BY, который выполняет группировку записей по вариациям заданного поля. То есть GROUP BY группирует все записи, в которых встречается одно и то же значение в указанном столбце, в одну строку. Так следующая команда выведет не количество тем, а их номера:

sqlite> SELECT theme FROM pages 
   ...> GROUP BY theme;
1
2
3

Таким образом мы можем узнать, на какие темы имеются страницы в базе данных.

Часто группировка и агрегирование фигурируют в одной команде. Например, надо выяснить количество записей в каждой группе:

sqlite> SELECT theme,count() 
   ...> FROM pages 
   ...> GROUP BY theme;

Здесь будут выведены два столбца. В первом будет номер темы, во втором – количество страниц темы. Функция count() будет выполняться после группировки по темам. Она будет считать количество записей в каждой теме.

На скрине в последней команде используется переименование столбца count() в столбец num. Делается это с помощью ключевого слова AS.

Пример совместного использования группировки и функции max():

sqlite> SELECT theme, max(num) 
   ...> FROM pages GROUP BY theme;
theme       max(num)  
----------  ----------
1           10        
2           100       
3           100 

Здесь сначала происходит группировка записей по темам. Потом в каждой группе ищется запись с максимальным значением столбца num.

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

sqlite> SELECT title,theme,max(num) 
   ...> FROM pages GROUP BY theme;
Amount of Information|1|10
Binary System|2|100
Boolean Lows|3|100

В примере выше это поле title. Однако подобное не всегда уместно:

sqlite> SELECT title, theme, count() 
   ...> FROM pages GROUP BY theme;
What is Information|1|2
Binary System|2|1
Boolean Lows|3|1

У нас две страницы первой темы, но поскольку мы выводим в одной строке целую группу, название другой страницы первой темы не показано. Таким образом, хотя можно указывать столбцы по которым группировка не выполняется, иногда в этом нет смысла.

Вычислить сумму частей столбца на основе другого столбца, используя QGIS Field Calculator?

Обновление: QGIS 2.18 (07.07.2017):

В Калькуляторе полей вы можете использовать функцию Aggregate,sum которая позволяет суммировать значения столбца на основе выражения. Вы также можете, при желании, группировать и фильтровать объекты для суммирования. Итак, в своем полевом калькуляторе вы можете просто использовать:

sum( "Sum", "Sales")

Что делает точно так же, как метод с использованием плагина GroupStats .

Оригинальное сообщение: 03/03/2016

Используйте плагин GroupStats, который вам может понадобиться загрузить из плагинов> Управление и установка плагинов .

Вот пример слоя с использованием некоторых атрибутов, которые вы показали:

Итак, начнем:

1. Запустите плагин GroupStats , выберите соответствующие поля для суммирования ( Salesдля строк; Sumдля значений) и нажмите кнопку « Рассчитать» . Вы должны увидеть результаты, показывающие сумму каждой продажи:

2. Сохраните результаты в виде файла .csv на панели инструментов GroupStats:

3. Импортируйте файл .csv обратно в QGIS, перетащив его в интерфейс или с панели инструментов:

   Слой> Добавить слой> Добавить текстовый слой с разделителями ( обратите внимание, что значения разделены точками с запятой )

4. Сделайте играя с шейп и файл .csv, сопоставляя Salesполя:

5. Теперь шейп-файл должен содержать значения суммы из файла .csv:

Вы можете, если хотите, перейти к использованию Save As...параметра в шейп-файле и сохранить его как новый, чтобы сохранить объединенные поля.

Агрегирующие функции

Агрегирующие функции

Агрегирующие
функции Функции, обрабатывающие набор строк для подсчета
и возвращения одного значения
используются для получения обобщающих значений. Их можно
применять ко
всем строкам таблицы, строкам, определенным в предложении WHERE или к
группам
строк в предложении GROUP BY. В любом случае, независимо от структуры
набора
строк, для каждого из них получается единственное значение.

Для выполнения
агрегирующих операций используются
следующие функции:

• COUNT определяет количество строк
  или значений поля, выбранных посредством запроса и не являющихся
  NULL-значениями;

  
  




• SUM вычисляет сумму всех выбранных значений данного поля;




• AVG вычисляет среднее значение для всех выбранных значений
  данного поля;




• МАХ вычисляет наибольшее из всех выбранных значений данного
  поля;




• MIN вычисляет наименьшее из всех выбранных значений данного
  поля.

В качестве
аргументов агрегирующих функций в большинстве случаев используются
названия
полей таблицы, но также допускаются константы, функции и любые их
комбинации с
арифметическими операторами.

Общая
форма агрегирующей функции имеет следующий вид:

aggregate_function
(<expr>)

Примеры

Найти средную стоимсть услуг:

SQL:


SELECT AVG(price) FROM tbl_service

В
результате выполнения запроса получим одно значение:

С
помощью следующего оператора можно найти среднюю стоимость всех услуг в
случае удвоения их
цены:

SQL:


SELECT AVG(price*2) FROM tbl_service

Если в
столбце, к которому применяется агрегирующая функция, имеются нулевые
(NULL)
значения, то они просто игнорируются.

Исключением является функция
COUNT(*), которая всегда подсчитывает общее количество строк,
независимо от
наличия в них нулевых значений.

Примеры

Подсчитать количество расторгнутых
договоров:

SQL:
SELECT COUNT(retire_date)
FROM tbl_contract

Результат:

Поскольку поле retire_date
содержит дату расторжения договора и значение NULL в
случае, если договор не был расторгнут, функция COUNT не учитывает
строки, содержащие значения NULL в поле retire_date.

SQL:


SELECT
COUNT(*) FROM tbl_contract

В результате выполнения данного запроса будет получено количество
записей в таблице tbl_contract:

Для
ограничения
числа строк, участвующих в агрегирующих вычислениях, можно использовать
предложение WHERE. В этом случае сначала выполняется предложение WHERE,
в
результате чего находятся все записи, удовлетворяющие заданным
условиям, а
затем к выбранным строкам применяются агрегирующие функции.

Если
условиям запроса не удовлетворяет ни одна строка, агрегирующие функции
возвращают значение NULL. Исключение составляет функции COUNT, которая
возвращает нуль.

Пример

Подсчитать количество клиентов мужского пола:

SQL:



SELECT COUNT(*) FROM tbl_clients WHERE sex=’m’

SQL SELECT SUM (), функция

Автор: Чайтанья Сингх | Файл: SQL

Функция SQL SUM () возвращает общую сумму значений числового столбца в таблице.

Функция СУММ () Синтаксис

 ВЫБРАТЬ СУММ (имя_столбца)
FROM table_name
ГДЕ условие; 

Пример функции SQL SUM () — в определенном столбце

В этом примере мы увидим, как узнать сумму значений числового столбца в таблице.

Таблица: Заказы

 ORDER_ID CUSTOMER_NAME ORDER_VALUE
-------- ------------- -----------
901 Ритеш 5000
911 Карл 9000
917 Кевин 85000
961 Пол 9000
998 Стив 6000 

Следующий оператор SQL находит сумму значений столбца ORDER_VALUE —

 ВЫБРАТЬ СУММУ (ORDER_VALUE)
ОТ Заказы; 

Результат:

 СУММА (ORDER_VALUE)
----------------
114000 

Функция СУММ () для нескольких столбцов

Мы можем использовать функцию SUM () для нескольких столбцов таблицы.

Таблица: клиенты

 CUST_ID CUSTOMER_NAME ORDER_VALUE PREV_BALANCE
-------- ------------- ----------- ------------
703 Ритеш 5000 2000
715 Карл 9000 3000
714 Кевин 85000 1000
760 Пол 9000 0
797 Стив 6000 5000 

Следующий оператор SQL находит сумму значений столбцов ORDER_VALUE и PREV_BALANCE —

 ВЫБРАТЬ СУММУ (ORDER_VALUE + PREV_BALANCE)
ОТ Заказчиков; 

Результат:

 СУММА (ORDER_VALUE + PREV_BALANCE)
-------------------------------
125000 

SQL SUM () с предложением where

Мы можем выборочно найти сумму только тех строк, которые удовлетворяют заданному условию.Для этого мы можем использовать предложение where в операторе SQL. Давайте рассмотрим пример, чтобы понять это.

Таблица: клиенты

 CUST_ID CUSTOMER_NAME ORDER_VALUE PREV_BALANCE
-------- ------------- ----------- ------------
703 Ритеш 5000 2000
715 Карл 9000 3000
714 Кевин 85000 1000
760 Пол 9000 0
797 Стив 6000 5000 

Следующий оператор SQL находит сумму значений столбца ORDER_VALUE, где ORDER_VALUE больше 8000 —

.

 ВЫБРАТЬ СУММУ (ORDER_VALUE)
ОТ клиентов
ГДЕ ORDER_VALUE> 8000; 

Результат:

 СУММА (ORDER_VALUE)
----------------
103000 

ПРИМЕР СУММЫ SQL () с DISTINCT

В этом примере мы увидим, как использовать DISTINCT с функцией SQL SUM ().

Таблица: Заказы

 ORDER_ID CUSTOMER_NAME ORDER_VALUE
-------- ------------- -----------
901 Ритеш 5000
911 Карл 9000
917 Кевин 85000
961 Пол 9000
998 Стив 6000 

Следующий оператор SQL находит сумму различных значений заказов из таблицы Orders. Здесь мы использовали псевдоним SQL для временного переименования имени столбца вывода.

 ВЫБРАТЬ СУММУ (РАЗЛИЧНОЕ ЗНАЧЕНИЕ_ЗАКАЗА) КАК "РАЗЛИЧНАЯ СУММА ЗАКАЗА"
ОТ Заказы; 

Результат:

 РАЗЛИЧНАЯ СУММА ЗАКАЗА
------------------
105000 

Объяснение: Поскольку значение заказа 9000 существует в таблице дважды, при нахождении суммы различных значений оно будет учитываться только один раз.

Функция СУММ SQL с предложением GROUP BY

Мы также можем использовать функцию SUM () с предложением GROUP BY.

Стол: Клиент

 CUST_ID CUSTOMER_NAME ORDER_VALUE
-------- ------------- -----------
703 Ритеш 5000
703 Ритеш 4000
703 Ритеш 1000
760 Пол 8000
760 Пол 6000 

Следующий оператор SQL находит сумму значений заказов для каждого отдельного клиента и группирует результат по имени клиента.

 SELECT CUSTOMER_NAME, SUM (ORDER_VALUE) КАК "Общий заказ"
ОТ Заказчика
ГРУППА ПО CUSTOMER_NAME; 

Результат:

 CUSTOMER_NAME Общий заказ
------------- ----------------
Ритеш 10000
Пол 14000 

Сервер

sql — сумма предыдущих n столбцов на основе некоторой категории

В SQL Server 2014 и 2016 ¹ вы можете использовать функцию WINDOW (т.е. предложение OVER ) для выполнения того, что вы хотите:

  ВЫБРАТЬ
     категория, год, неделя, значение,
     sum (value) OVER (РАЗДЕЛЕНИЕ ПО категориям
                          ЗАКАЗАТЬ ПО годам, неделям
                              СТРОКИ 2 ПРЕДЫДУЩИЙ) КАК retention_value_3_weeks
ИЗ
     т
СОРТИРОВАТЬ ПО
    категория, год, неделя;
  

И вот результат:

категория | год | неделя | значение | retention_value_3_weeks
: ------- | ---: | ---: | ----: | ----------------------:
а | 2016 | 1 | 5 | 5
а | 2016 | 2 | 7 | 12
а | 2016 | 3 | 8 | 20
б | 2016 | 3 | 6 | 6
б | 2016 | 4 | 15 | 21 год
б | 2016 | 5 | 25 | 46
c | 2016 | 3 | 25 | 25
c | 2016 | 4 | 2 | 27
c | 2016 | 5 | 21 | 48
c | 2016 | 6 | 26 | 49
 

ЗАМЕТЬТЕ, что x = 3 вашего примера преобразуется в ( текущая строка и 2 предшествующие строки ).

Если по какой-то причине вы не можете использовать предложение OVER , вы все равно можете вычислить те же результаты, используя некоторые (довольно запутанные) подзапросы:

  ВЫБРАТЬ
    категория, год, неделя, значение,
    (ВЫБРАТЬ
        сумма (значение)
     ИЗ
        (ВЫБЕРИТЕ TOP 3 / * общее количество строк для рассмотрения * /
             ценить
        ИЗ
           t t2
        ГДЕ
               t2.category = t.category / * раздел по категориям * /
           И t2.week <= t.неделя / * текущая и предыдущая строки * /
        СОРТИРОВАТЬ ПО
           год DESC, неделя DESC / * упорядочить по критериям * /
        ) AS q
     ) AS retention_value_3_weeks
ИЗ
    т
СОРТИРОВАТЬ ПО
    категория, год, неделя;
  

Проверьте все на dbfiddle здесь

Если вы хотите использовать @x вместо 3 , вы можете сделать это:

  DECLARE @x AS INTEGER = 3;

ВЫБРАТЬ
    категория, год, неделя, значение,
    (ВЫБРАТЬ
        сумма (значение)
     ИЗ
        (SELECT TOP (@x) / * общее количество строк для рассмотрения * /
             ценить
        ИЗ
           t t2
        ГДЕ
               t2.category = t.category / * разделение по категориям * /
           AND t2.week <= t.week / * текущая и предыдущие строки * /
        СОРТИРОВАТЬ ПО
           год DESC, неделя DESC / * упорядочить по критериям * /
        ) AS q
     ) AS retention_value_3_weeks
ИЗ
    т
СОРТИРОВАТЬ ПО
    категория, год, неделя ;;
  

dbfiddle здесь

¹⁾ Невозможно протестировать с SQL Server 2012, потому что у меня его нет. В документации от MS SQL Server указано, что должен быть доступен с версии 2008.

mysql - SUM () игнорирует GROUP BY и суммирует 4 строки вместо 2

Как сказал Виллем Рензема, вы неправильно поняли, как работает GROUP BY . Поскольку не похоже, что вы поняли то, что он сказал, позвольте мне сказать немного по-другому.

GROUP BY , вполне логично, используется для группировки строк из вашего набора результатов. Обычно вы предоставляете список столбцов для группировки строк. GROUP BY sch.id, cv.member_id указывает SQL идентифицировать уникальные наборы значений для этих двух столбцов и группировать строки в результирующем наборе по этим значениям.В вашем случае для этих двух значений есть две уникальные пары значений:

• cv.member_id = 82, sch.id = 17101
• cv.member_id = 82, sch.id = 17153

Итак, вы получите две группы строк - три с первой парой значений и одну со второй парой.

Добавление дополнительных столбцов в предложение GROUP BY не приведет к и никогда не приведет к уменьшению количества групп - либо новый столбец (и) одинаковы во всех строках (в этом случае у вас одинаковое количество групп), либо новый столбец (s) имеют разные значения в некоторых строках в одной или нескольких часовых исходных группах (в этом случае у вас теперь будет больше групп).

Также (как указал Виллем) у вас есть синтаксическая ошибка. Столбцы в списке GROUP BY разделены запятыми. В вашем GROUP BY sch.id И cv.member_id вы группируете по вычислению: sch.id И cv.member_id , или результат обработки sch.id и cv.member_id как если это были логические значения. Поскольку ни один из них не равен 0, при преобразовании в логические значения оба оцениваются как 1 (истина), а комбинация (истина И истина) истинна.Итак, у вас получается всего одна группа из 4 рядов.

Давайте сделаем шаг назад и рассмотрим, что (похоже) вы на самом деле пытаетесь сделать. Для данного member_id вам нужно общее время, в течение которого они были вовлечены в действия типа «Зарезервировано» или «Настоящее».

Обратите внимание, что общее время рассчитывается из таблицы schedule_event . Также обратите внимание, что данный member_id может быть связан с одним и тем же schedule_event более одного раза. Итак, чтобы получить общее время, нам нужно идентифицировать отдельные строки schedule_event , к которым привязан наш member_id , и просуммировать время для этих уникальных значений.

В этом случае самый простой способ продолжить - использовать подзапрос для получения списка отдельных событий schedule_events , к которому привязан наш member_id , а затем суммировать общее время для этих отдельных событий.

Вот запрос, который сделает именно это:

  ВЫБЕРИТЕ `member_id`
      , СУММ (`totalTime`) как` totalTime`
  ИЗ (
        ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ
            cv.member_id AS `member_id`,
            sch.id AS `scheduleId`,
            TIMESTAMPDIFF (ВТОРОЙ, сч.start_at, sch.ends_at) AS `totalTime`
        ИЗ
            `schedule_event` AS` sch`
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ `client_visit` AS` cv` ON cv.schedule_event_id = sch.id
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ `type_of_visit` AS` tov` ON tov.id = cv.status_type_id
        ГДЕ
            (tov.type = 'TYPE_BOOKED' ИЛИ ​​tov.type = 'TYPE_PRESENT')
            И cv.member_id = 82
       ) sq
 ГРУППА ПО `member_id`;
  

Подзапрос (образно обозначенный как sq ), по сути, является вашим исходным запросом.Я изменил ваш LEFT JOIN на INNER JOIN , так как у нас должна быть запись client_visit , чтобы идентифицировать как member_id , так и тип посещения. Однако я удалил SUM на totalTime ; на данный момент мы просто хотим знать время, которое займет каждое событие schedule_event . Я также добавил DISTINCT - нам все равно, сколько раз этот schedule_event появляется с этим member_id ; общее время будет одинаковым независимо от того, появится ли оно один, три или 207 раз.

После того, как мы определили данные schedule_event , к которым подключен наш member_id , нам нужно общее время для всех строк schedule_event . Итак, мы берем результаты подзапроса, группируем их по member_id (на тот случай, если когда-либо потребуется отменить это для нескольких значений member_id ) и суммируем вычисленное время для каждой строки schedule_event .

Так как у Джоаноло возникли проблемы с настройкой dbfiddle для вашей проблемы, я взял его работу и добавил этот запрос в конце, чтобы вы могли видеть, что результаты были именно такими, как вы хотели; обновленная ссылка dbfiddle находится здесь.

Надеюсь, это поможет прояснить, как на самом деле работает для вас GROUP BY .

Sum sql для данных в нескольких столбцах и строках с Total & Percentage

Мы видели, как функция суммы используется для получения общего значения столбца в таблице mysql. Теперь мы узнаем, как получить запрос суммы в нескольких столбцах и для каждой записи таблицы. Для лучшего понимания мы немного изменим нашу таблицу учеников, добавив оценки по разным предметам для каждой записи.Теперь таблица будет такой.

78

Макс 2

903 903

903 14 903 903 14 903

6614 903 903 Большой Джон 903

• Видеоурок по сумме значений столбца с использованием GROUP BY, IN & CASE

Полная таблица студентов с несколькими столбцами Дамп SQL
Напишем запрос для отображения суммы всех оценок каждого ученика.Обратите внимание, что здесь мы не будем использовать функцию sql суммы внутри нашего запроса. Вот код.

  SELECT id, name, class, (social + science + math) AS всего
ИЗСум_студента  

9

903

Несколько столбцов СУММ

Как видите, указанная выше сумма - это сумма оценок каждого студента.Это горизонтальная сумма в нашей таблице. Теперь попробуем получить сумму оценок всех учеников по всем предметам. Таким образом, сумма должна быть собрана во всех трех столбцах.

  ВЫБЕРИТЕ сумму (общественные науки + математика + естественные науки) как общую
ИЗСум_студента  

Мы можем добавить различные другие команды sql, такие как предложение where и т. Д.

Расчет процента оценок

Предположим, что полная оценка по каждому предмету составляет 95. Итак, чтобы получить процентную оценку каждого ученика, мы должны умножить 95 на 3 (для трех предметов), а затем разделить полученное значение от общей суммы и умножить на 100. Вот запрос.

  ВЫБЕРИТЕ имя, ((общественные + естественные + математические) / (95 * 3) * 100) КАК процент
 ОТ `student_sum`  

Отображение итоговой оценки с процентами

И общую оценку, и процент мы можем отобразить следующим образом

  ВЫБЕРИТЕ id, имя, класс, (общественные науки + наука + математика) как Итого,
((общественные науки + наука + математика) / (95 * 3) * 100) AS в процентах
 ОТ `student_sum`  

Результат здесь.

4737

903

Приведенные выше процентные данные имеют четыре десятичных разряда, мы можем отформатировать эти числа с помощью команды ROUND Sql.Запрос на отображение процентного значения с использованием двух десятичных знаков.

  ВЫБЕРИТЕ id, имя, класс, (общественные науки + наука + математика) как Итого,
ФОРМАТ (((общественные + естественные + математические) / (95 * 3) * 100), 2) В процентах
  ОТ `student_sum`  

Сумма данных строки каждой записи

Теперь мы попытаемся отобразить все оценки и их сумму для каждого студента, используя group by. Обратите внимание, что id - это столбец уникального значения в нашей таблице. Вот запрос

  ВЫБРАТЬ идентификатор, имя, класс, социальный, математический, естественный,
 сумма (социальная + математика + наука) как Итого
 from student_sum ГРУППА ПО id  

Результат здесь

903

903 14 903

903

вместе с агрегированными функциями Listing , max () с помощью over () с разделением →

Отображение от самого высокого до самого низкого

Мы можем составлять список, начиная с самой высокой отметки до самой низкой, используя запрос «Порядок по запросу».

  ВЫБРАТЬ идентификатор, имя, класс, социальный, математический, естественный,
 сумма (социальная + математика + наука) как Итого
 from student_sum ГРУППА ПО id ЗАКАЗАТЬ Итого DESC  

Отображение самой высокой и самой низкой оценки по всем предметам каждого учащегося

  SELECT id, name, GREATEST (общественные науки, математика) как max,
НАИМЕНЕЕ (общественные науки, математика) как минимум ОТ student_sum;  

903 Арнольд

9

903

Отображение суммы всех столбцов в последней строке

Как и в MS Excel, мы можем отображать сумму всех столбцов и сумму всех общих предметных оценок с помощью UNION.Обратите внимание, что при использовании запроса UNION мы должны поддерживать одинаковое количество столбцов.

  ВЫБРАТЬ идентификатор, имя, класс, социальный, математический, естественный,
 сумма (социальная + математика + наука) как Итого
 from student_sum ГРУППА ПО id
СОЮЗ
ВЫБРАТЬ '', '', 'Итого', сумма (социальная), сумма (математика), сумма (наука),
сумма (социальная) + сумма (математика) + сумма (наука) from student_sum  

Вывод (смотрите последнюю строку)

903

903 14 903

903

9031

Показать оценку на основе общей оценки (с использованием оператора CASE)

Мы можем отобразить оценку учащегося, используя оператор CASE в нашем запросе.MySQL Case Запрос выполняет инструкцию на основе условия сопоставления. Здесь мы сверим общую оценку с установленными отметками для разных уровней оценки и присвоим ей оценку соответственно.

Подробнее о заявлении CASE WHEN THEN здесь.

Вот наш запрос.

  ВЫБРАТЬ идентификатор, имя, класс, социальный, математический, естественный,
 сумма (социальная + математика + наука) как Итого,
СЛУЧАЙ, КОГДА сумма (социальная + математика + естественные науки)> = 225 ТОГДА 'A'
 КОГДА сумма (социальная + математика + наука)> = 200 ТОГДА 'B'
КОГДА сумма (социальная + математика + наука)> = 170 ТОГДА 'C'
ИНАЧЕ 'FAIL'
Оценка END AS

from student_sum ГРУППА ПО id  

Вывод здесь.

903

80311

Сохранение общей оценки в другой таблице.

Мы можем хранить сумму оценок по каждому предмету каждого студента в отдельной таблице. Это будет полезно для печати или отображения окончательного отчета.

Сохранение суммы каждой записи в другой таблице

Код PHP для отображения

Мы можем выполнить приведенный выше код и отобразить записи с помощью PHP. В коде сначала мы подключимся к базе данных MySQL.

  
требуется "config.php"; // Подключение к базе данных

$ count = "ВЫБРАТЬ id, имя, социальное, математическое, естественное, сумму (социальное + математическое + естественное) как общее из группы student_sum по идентификатору";

echo "<таблица>";
echo "  id   name   социальная   математика   наука   всего < / th>  ";

foreach ($ dbo-> query ($ count) as $ row) {
echo "  $ row [id]   $ row [name]   $ row [social]   $ row [math] < / td>  $ row [наука]   $ row [всего]   ";
}
эхо "";
?>  

Список всех столбцов вместе с агрегатными функциями, такими как sum (), max (), используя over () с partition →

Полная таблица учеников с SQL-дампом
SQL-файл для запроса SUM

← Запрос суммы математических ссылок SQL →
Добавить сумму в другую таблицу →

• Видеоурок по размещенным ниже вопросам с ответами

Эта статья написана plus2net.com team.

6. Выучите SQL COUNT / SUM

Пора научиться управлять числовыми данными.
Давайте узнаем, как использовать COUNT и SUM .

🆕 🌟
📌 COUNT: найти количество строк, которые соответствуют заданным критериям
📌 SUM: найти общую сумму числового столбца

# Содержание

Запрос 1. Использование COUNT

  ВЫБРАТЬ СЧЕТЧИК (*) ОТ адреса; ВЫБРАТЬ СЧЕТЧИК (адрес2) ИЗ адреса;  

Запрос 2.Использование COUNT с DISTINCT

  SELECT COUNT (rental_duration) FROM film;
ВЫБЕРИТЕ КОЛИЧЕСТВО (ОТЛИЧИТЕЛЬНО прокат_длительности) ИЗ фильма;  

Запрос 3. Использование SUM

  SELECT SUM (длина) FROM film;
ВЫБРАТЬ СУММУ (длина) / 60 КАК весь_час из фильма;  

# COUNT

Q1. Нахождение количества всех столбцов и столбцов address2 в таблице адресов

SELECT COUNT (имя столбца)

Вы помните, как просматривать все столбцы в таблице? Мы должны использовать SELECT * FROM имя таблицы .Если нам нужно только количество столбцов, а не сами данные, мы можем использовать новый запрос COUNT .

Добавьте COUNT после SELECT , и, поскольку мы хотим видеть количество строк (данных) во всех столбцах, не забудьте использовать звездочку ( * ).

  ВЫБРАТЬ СЧЕТЧИК (*) из адреса;
  

Что, если бы мы хотели видеть только количество строк в определенной категории? Тогда вместо звездочки мы бы написали имя столбца в круглых скобках.Давайте попробуем address2:

  SELECT COUNT (address2) FROM address;  

COUNT (*) вычисляет количество столбцов, включая нулевое значение. Если вы хотите узнать количество столбцов без нулевого значения, вы должны написать конкретное имя столбца в круглых скобках.

2 кв. Нахождение количества всех столбцов и уникальных столбцов продолжительности проката в таблице фильмов

SELECT COUNT (DISTINCT name)

Если мы хотим увидеть количество уникальных значений в столбце, мы можем использовать DISTINCT , которую мы узнали в прошлом уроке.

Давайте посмотрим, как мы можем использовать это с COUNT .

Вы можете подсчитать количество всех строк в продолжительности аренды, используя SELECT COUNT (rental_duration) . Но что, если вы хотите узнать количество уникальных значений в столбце? В практическом примере , который мы использовали в прошлом руководстве, мы видели значения 3, 4, 5, 6 и 7 (часы). Всего существует пяти различных периодов продолжительности аренды .

Вы можете найти это «общее количество», если используете DISTINCT с SELECT COUNT .Таким образом, вы можете увидеть результат 5 , используя этот оператор:

  ВЫБРАТЬ СЧЕТ (DISTINCT rental_duration) FROM film;  

Если вы не используете запрос DISTINCT , вы получите общее количество различных периодов аренды. Взгляните на картинку ниже для сравнения:

# SUM

Q3. Нахождение общей длины всех пленок в таблице фильмов

ВЫБРАТЬ СУММ (имя столбца)

Теперь мы попробуем найти общую длину всех пленок, используя СУММ .

SUM имеет ту же структуру, что и COUNT . Вы можете написать SUM сразу после SELECT и имени столбца в круглых скобках следующим образом:

  SELECT SUM (length) FROM film;  

Вы можете увидеть число 115272 в результатах, но мы не знаем, сколько часов это равно, поскольку отображаемое значение выражено в минутах. Давайте разделим его значение на 60, чтобы увидеть его по часам, и добавим новое имя столбца, используя AS .

🔑 Мы можем использовать символы ( + ), ( - ), ( * ), (/) в SQL, когда данные являются числовыми. Таким образом, мы можем увидеть общую длину всех фильмов, используя СУММ (длина) / 60 . Просто добавьте AS all_hour , чтобы переименовать новый столбец окончательного результата.

  ВЫБРАТЬ СУММУ (длина) / 60 КАК весь_час из фильма;  

# Время практики

Сегодня ваша задача - найти количество уникальных значений replacement_cost в таблице фильма.Это довольно просто, поэтому давайте воспользуемся тем, что мы уже узнали, чтобы найти значения!

Мы должны использовать COUNT и DISTINCT вместе, чтобы увидеть количество уникальных значений. Если мы используем здесь только DISTINCT , нам придется подсчитывать результат вручную, поэтому лучше использовать COUNT , чтобы упростить задачу.

  ВЫБРАТЬ СЧЕТЧИК (РАЗЛИЧНАЯ цена_замены) ИЗ пленки;  

Результат: 21 →, что означает, что у нас есть 21 различное значение (в данном случае 21 различная цена за замену фильмов) в указанном столбце.

Расчет промежуточной суммы с предложением OVER и разделением по условию в SQL Server

Часто встречаются сценарии, в которых необходимо вычислить промежуточную сумму количества.

Под промежуточным итогом понимается сумма значений во всех ячейках столбца, который предшествует следующей ячейке в этом конкретном столбце.

Давайте рассмотрим пример, чтобы прояснить это.

Как вы можете видеть, третья строка столбца RunningAgeTotal содержит сумму всех значений в строках от 1 ^st до 3 ^rd столбца StudentAge, т.е.е. 14 + 12 + 13 = 39.

Аналогично, значение строки 4 ^-й столбца RunningAgeTotal равно 49, что является суммой значений в строке 1 ^-й до 4-х ^{-й строки} столбца StudentAge.

В SQL Server предложение OVER можно использовать для вычисления промежуточных итогов.

Давайте рассмотрим, как это использовать, на примере ниже.

Простой пример расчета промежуточной суммы

Давайте создадим фиктивные данные перед написанием запроса, который вычисляет промежуточную сумму.

Сначала выполните следующий сценарий:

  СОЗДАТЬ БАЗУ ДАННЫХ Школа
ИДТИ

ЕГЭ школа
ИДТИ

СОЗДАТЬ ТАБЛИЦУ Студенты
(
Id INT ИДЕНТИЧНОСТЬ ПЕРВИЧНОГО КЛЮЧА,
StudentName VARCHAR (50),
StudentGender VARCHAR (50),
StudentAge INT
)
ИДТИ

ВСТАВИТЬ В ЦЕННОСТИ студентов («Салли», «Женщина», 14 лет)
ВСТАВИТЬ ЦЕННОСТИ студентов («Эдвард», «Мужской», 12)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Джон», «Мужчина», 13)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Лиана», «Женщина», 10)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Бен», «Мужчина», 11)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Элис», «Женщина», 12)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Ник», «Мужской», 9)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Джош», «Мужчина», 12)
ВСТАВИТЬ В ЦЕННОСТИ студентов («Лиза», «Женщина», 10)
ВСТАВИТЬ ЦЕННОСТИ студентов («Фитиль», «Мужской», 15)  

Этот сценарий создает таблицу «Студенты» в базе данных School.В таблице четыре столбца: Id, StudentName, StudentGender и Student. Оператор INSERT добавляет в базу данных 10 фиктивных записей.

Чтобы вычислить промежуточную сумму, мы должны использовать предложение OVER и добавить столбец, для которого мы хотим вычислить промежуточную сумму. Следующий скрипт вычисляет промежуточную сумму значений в столбце StudentAge и добавляет результат в столбец RunningAgeTotal.

  ЕГЭ Школа
ВЫБЕРИТЕ Id, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (ORDER BY Id) AS RunningAgeTotal
ОТ Студентов  

В приведенном выше сценарии оператор SELECT извлекает столбцы StudentName, StudentGender и StudentAge вместе со столбцом промежуточных итогов, т.е.е. RunningAgeTotal. Функция SUM Aggregate добавляет значения в столбец StudentAge, а предложение OVER определяет, что добавление должно выполняться в форме промежуточной суммы, упорядоченной по столбцу Id. Результат вышеупомянутого скрипта выглядит следующим образом:

Расчет скользящего среднего

Вы можете изменить сценарий в последнем разделе, чтобы вычислить средний возраст всех учащихся в таблице «Студенты». Для этого выполните следующий скрипт:

  ЕГЭ Школа
ВЫБЕРИТЕ Id, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (ORDER BY Id) AS RunningAgeTotal,
СРЕДНИЙ (Возраст учащихся) ВЫШЕ (В ЗАКАЗЕ ПО Id) КАК RunningAgeAverage
ОТ Студентов  

Как видите, мы используем агрегатную функцию AVG для вычисления среднего возраста всех студентов в столбце StudentAge.Результат вышеупомянутого скрипта выглядит так:

Взгляните на третью строку столбца RunningAgeAverage. Он содержит среднее значение от 1 ^st до 3 ^rd строк в столбце StudentAge, то есть (14 + 12 + 13) / 3 = 13.

Разделение промежуточной суммы по значениям столбцов

Вы также можете рассчитать промежуточную сумму, разделив данные по значениям в определенном столбце. Например, вы можете рассчитать промежуточную сумму возраста учащихся с разбивкой по полу.Для этого вы должны использовать оператор PARTITION BY вместе с предложением OVER.

Взгляните на следующий пример:

  ЕГЭ Школа
ВЫБЕРИТЕ Id, StudentName, StudentGender, StudentAge,
SUM (StudentAge) OVER (РАЗДЕЛЕНИЕ ПО StudentGender ЗАКАЗАТЬ ПО Id) КАК RunningAgeTotal
ОТ Студентов  

Единственное различие между вычислением промежуточной суммы для всех записей и вычислением промежуточной суммы по полу состоит в использовании предложения PARTITION BY StudentGender в скобках после предложения OVER.Приведенный выше сценарий вычисляет промежуточную сумму для значений в столбце StudentAge, разбитых по значениям в столбце StudentGender. Результат выглядит так.

Теперь взгляните на первые четыре значения в столбце RunningAgeTotal (выделены красным прямоугольником). Эти значения представляют собой промежуточную сумму учащихся женского пола. Точно так же последние 6 строк (выделены зеленым прямоугольником) содержат промежуточный возраст учащихся мужского пола в таблице «Студенты».

Проблемы с OVER, когда столбец имеет повторяющийся столбец

Проблема возникает, если столбец с повторяющимися значениями используется с предложением OVER для вычисления промежуточной суммы. Взгляните на столбец StudentAge. У Элис, Эдварда и Джоша один и тот же возраст, то есть 12. Точно так же Лиана и Лиза имеют одинаковые значения в столбце StudentAge, то есть 10.

Если вы попытаетесь подсчитать промежуточную сумму, указав столбец StudentAge в скобках после предложения OVER, вы увидите некоторые странные результаты.Запустим этот запрос:

  ЕГЭ Школа
ВЫБЕРИТЕ Id, StudentName, StudentGender, StudentAge,
СУММА (возраст студента) ВЫШЕ (ЗАКАЗ ПО возрасту студента) КАК RunningAgeTotal
ОТ Студентов  

Результат вышеуказанного запроса выглядит следующим образом:

Во второй строке столбца RunningAgeTotal значение 29. Однако оно должно быть 19, потому что строки 1 ^st и 2 ^и столбца StudentAge содержат 9 и 10 соответственно. В этом случае, поскольку и 2 строки ^nd , и 3 строки ^rd столбца StudentAge содержат повторяющееся значение, т.е.е. 10 значение для строки 2 ^и столбца RunningAgeTotal вычисляется путем сложения 9, 10 и 10. Аналогично, для строки 3 ^rd столбца RunningAgeTotal значение из второй строки, равное 29, равно использовал.

Аналогичным образом, если вы посмотрите на строку 5 ^th столбца RunningAgeTotal, значение будет 76. Фактически оно должно быть 40 + 12 = 52. Однако, поскольку 5 ^th , 6 ^th, и 7 ^th строк столбца StudentAge имеют повторяющиеся значения, т.е.е. 12, промежуточная сумма рассчитывается путем сложения 40 + 12 + 12 + 12 = 76. Эта промежуточная сумма использовалась для строк 6 ^th и 7 ^th столбца RunningAgeTotal, поскольку строки 6 ^th и 7 ^th столбца StudentAge содержат повторяющиеся значения в строке 5.

Чтобы избежать этой ситуации, необходимо прекратить использование столбцов с повторяющимися значениями вместе с предложением OVER. Столбец Первичный ключ всегда является хорошим выбором для использования с предложением OVER, поскольку он содержит только уникальные значения.

Также читают:

Группирование данных с использованием функций OVER и PARTITION BY

Уроки по использованию OVER и PARTITION BY

Бен Ричардсон руководит организацией Acuity Training. Acuity - это компания, занимающаяся обучением в сфере ИТ, предлагающая аудиторные курсы в Лондоне и Гилфорде. Это ведущий поставщик обучения SQL в Великобритании и предлагает полный спектр обучения SQL от вводного обучения до продвинутых курсов администрирования. Последние сообщения Бена Ричардсона (посмотреть все)

Как сгруппировать и суммировать результаты (простое объяснение)

На сегодняшнем уроке вы узнаете о группировке результатов, полученных по вашим запросам, с помощью предложения GROUP BY.

Цели сегодняшнего урока:

• Узнайте, как группировать результаты с помощью GROUP BY
• Использование агрегатных функций для выполнения вычислений
• Узнайте, как фильтровать группы с помощью предложения HAVING

GROUP BY Пункт

Предложение SQL GROUP BY используется для вывода строки по указанным значениям столбца. Обычно он используется вместе с агрегатными функциями, такими как SUM или Count, для суммирования значений. В группах SQL есть уникальные комбинации полей.Вместо того, чтобы возвращать каждую строку в таблице, когда значения сгруппированы, возвращаются только уникальные комбинации.

[youtube https://www.youtube.com/watch?v=4m1k3_INSqU?rel=0]

Предложение GROUP BY добавляется к оператору SQL после предложения WHERE. Вот пример, в котором мы указываем OrderID без учета количества, превышающего 100.

 ВЫБРАТЬ ИД заказа
ОТ OrderDetails
ГДЕ Количество <= 100
ГРУППА ПО ИДЕНТИФИКАТОРУ ЗАКАЗА; 

Следует отметить несколько моментов.Во-первых, столбцы, которые мы хотим подытожить, перечислены через запятую в предложении GROUP BY. Во-вторых, тот же список столбцов должен быть указан в операторе select; в противном случае утверждение не выполняется.

При запуске этого оператора возвращается не каждая отфильтрованная строка. В результат включаются только уникальные комбинации OrderID. Этот оператор очень похож на

.

 ВЫБРАТЬ ОТЛИЧИТЕЛЬНЫЙ ИД ЗАКАЗА
ОТ OrderDetails
ГДЕ Количество <= 100; 

Но есть ключевое отличие.Модификатор DISTINCT останавливается на выводе уникальной комбинации строк, тогда как с помощью оператора GROUP BY мы можем вычислять значения на основе базовых отфильтрованных строк для каждой уникальной комбинации.

Другими словами, используя наш пример с GROUP BY, мы можем вычислить количество или OrderDetails для каждого заказа следующим образом:

 ВЫБЕРИТЕ ИД заказа, СЧЁТ (ИД заказа) как NumOrderDetails
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАТОРУ ЗАКАЗА; 

COUNT - это пример агрегатной функции, именно они придают оператору GROUP BY особое значение.

Агрегатные функции

Некоторые функции, такие как СУММ, используются для выполнения вычислений над группой строк, они называются агрегатными функциями. В большинстве случаев эти функции работают с группой значений, определенных с помощью предложения GROUP BY. Когда нет предложения GROUP BY, обычно понимается, что агрегатная функция применяется ко всем отфильтрованным результатам.

Некоторые из наиболее распространенных агрегатных функций включают:

Эти функции могут использоваться сами по себе в сочетании с предложением GROUP BY. Сами по себе они действуют по всему столу; однако при использовании с GROUP BY их вычисления «сбрасываются» при каждом изменении группировки. Таким образом, они действуют как промежуточные итоги.

Общий синтаксис агрегатной функции

При использовании агрегатной функции вы можете вычислить результат для всех значений или отдельных значений. Например, для подсчета всех записей OrderDetails можно использовать выражение:

 ВЫБРАТЬ СЧЕТЧИК (идентификатор заказа)
ОТ OrderDetails; 

Для подсчета отдельных заказов, составляющих детали, мы использовали бы следующее:

 ВЫБРАТЬ СЧЕТЧИК (РАЗЛИЧНЫЙ ИД ЗАКАЗА)
ОТ OrderDetails; 

Использование агрегатных функций с GROUP BY

[youtube https: // www.youtube.com/watch?v=z24_YYNVwig?rel=0]

AVG и SUM

Функция СУММ суммирует возвращенные значения, аналогично AVG вычисляет среднее значение.

Давайте посмотрим, сможем ли мы рассчитать общую сумму заказа на основе OrderDetails. Из предыдущих уроков мы знаем, как рассчитать общую сумму для каждой детали как:

 ВЫБЕРИТЕ OrderID, ProductID, UnitPrice * Количество как TotalPrice
ОТ OrderDetails; 

Поскольку мы можем применить агрегатную функцию к выражениям, мы можем настроить группировку по OrderID для расчета общей цены за заказ как

 ВЫБЕРИТЕ OrderID, SUM (UnitPrice * Quantity) как TotalPrice
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАТОРУ ЗАКАЗА; 

Мы даже можем отсортировать по сумме, чтобы получить первые заказы

 ВЫБЕРИТЕ OrderID, SUM (UnitPrice * Quantity) как TotalPrice
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАЦИИ ЗАКАЗА
ЗАКАЗАТЬ ПО TotalPrice DESC; 

Аналогичным образом мы можем рассчитать среднюю сумму детализации заказа как

 ВЫБРАТЬ ИД заказа,
       AVG (UnitPrice * Quantity) как AverageOrderAmount
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАТОРУ ЗАКАЗА; 

Для любопытных, поскольку среднее значение рассчитывается как сумма выборки, деленная на количество выборок, использование AVG в приведенном выше утверждении аналогично:

 ВЫБРАТЬ ИД заказа,
       SUM (UnitPrice * Quantity) / COUNT (OrderID) как AverageOrderAmount
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАТОРУ ЗАКАЗА; 

В этом разделе мы рассмотрели многое.Вот некоторые ключевые моменты, о которых следует помнить:

1. Агрегатная функция может оценивать такое выражение, как SUM (A + B)
2. Вы должны использовать псевдонимы агрегатных функций, чтобы имена столбцов были значимыми
3. При работе с агрегатными функциями и GROUP BY иногда легче сначала подумать о деталях, то есть написать простой оператор SELECT, проверить результаты, а затем добавить причудливые вещи.

СЧЕТ

Функция COUNT используется, когда вам нужно узнать, сколько записей существует в таблице или в группе.COUNT (*) будет считать каждую запись в группе; тогда как COUNT (выражение) считает каждую запись, в которой результат выражения не равен нулю. Вы также можете использовать Distinct с COUNT, чтобы найти количество уникальных значений в группе.

Чтобы узнать количество строк OrderDetail в заказе

 ВЫБРАТЬ ИД заказа, СЧЁТ (ИД заказаДетали)
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАТОРУ ЗАКАЗА; 

Узнать количество уникальных заказов на товар

 ВЫБЕРИТЕ ИД продукта, СЧЁТ (ОТЛИЧНЫЙ ИД заказа)
ОТ OrderDetails
ГРУППА ПО ProductID; 

МИН и МАКС

Используйте MIN и MAX, чтобы найти наименьшее и наибольшее значения, соответственно, в таблице или группе.

Например, чтобы найти наименьшее и наибольшее количество товаров, заказанных в рамках заказа, попробуйте

 ВЫБРАТЬ ИД заказа,
       MIN (количество) как MinQuantity,
       MAX (количество) как MaxQuantity
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАТОРУ ЗАКАЗА; 

Вы также можете найти МИН или МАКС значение вычисления. Здесь мы находим наибольшее количество продукта, заказанного в рамках продукта:

 ВЫБРАТЬ ИД заказа,
       MAX (UnitPrice * Quantity) как MaxAmount
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАТОРУ ЗАКАЗА; 

ИМЕЕТ пункт

Предложение HAVING используется для фильтрации групп в соответствии с результатами агрегатных функций.Это позволяет решать такие проблемы, как выбор всех заказов, у которых более двух строк детализации заказа.

Этот пример выглядит как

 ВЫБРАТЬ ИД заказа, СЧЁТ (ИД заказаДетали)
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАЦИИ ЗАКАЗА
ИМЕЕТ СЧЕТ (OrderDetailID)> 2; 

Если бы мы хотели найти все заказы на сумму более 1000 долларов, мы бы написали

 ВЫБРАТЬ ИД заказа,
       SUM (UnitPrice * Quantity) как TotalPrice
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАЦИИ ЗАКАЗА
ИМЕЕТ TotalPrice> 1000
ЗАКАЗАТЬ ПО TotalPrice DESC; 

[youtube https: // www.youtube.com/watch?v=LdaZTMtI2Wc?rel=0]

Этот запрос аналогичен запросу из предыдущего раздела с добавлением предложения HAVING. Мы могли бы записать предложение HAVING как

 ИМЕЕТ СУММУ (UnitPrice * Quantity)> 1000 

Но, поскольку столбец уже имеет псевдоним, мы использовали его.

Чтобы забить домой ИМЕЮЩИЙ, хочу показать один последний пример. Здесь вы увидите, что оператор HAVING включает агрегатную функцию, которой нет в списке SELECT.

 ВЫБРАТЬ ИД заказа,
       SUM (UnitPrice * Quantity) как TotalPrice
ОТ OrderDetails
ГРУППА ПО ИДЕНТИФИКАЦИИ ЗАКАЗА
ИМЕЕТ СРЕДНЕЕ (Цена единицы * Количество)> 500
ЗАКАЗАТЬ ПО TotalPrice DESC; 

В приведенном выше запросе мы получаем общую стоимость заказов, в которых средняя сумма OrderDetail превышает 500 долларов США.00.

Заключительное заявление о ИМЕЕТ

Хотя они выполняют схожую функцию, есть ключевое различие между предложением WHERE и HAVING. Предложение WHERE фильтрует отдельные записи; тогда как предложение HAVING фильтрует группы.

Чтобы держать это прямо в голове, мне нравится думать о предложении WHERE, выполняющем свою работу до того, как произойдут какие-либо группировки, а затем о предложении HAVING, которое вступает в силу после того, как группы сформированы.

Упражнения

Важно практиковаться! Используйте образец базы данных, чтобы ответить на эти вопросы.

1. Какое среднее количество заказывается в таблице OrderDetails?
2. Отобразите минимальное, максимальное и среднее количество заказанных для каждого продукта в OrderDetails.
   

   No related posts.