Как исправить ошибку ‘Столбец недействителен в списке select, потому что он не содержится ни в агрегатной функции, ни в предложении GROUP BY’

Содержание:

• Group by clause:
• Группировка по стратегии Split-Apply-Combine:
• Пример:
• Ошибка: Column ’employee.sallary’ недопустим в списке select, потому что он не содержится ни в агрегатной функции, ни в предложении GROUP BY.
• Решение:
• Агрегатные функции:
• Логическое описание использования функций group by и aggregate вместе:
• Ошибка: Колонка ‘люди.age’ недопустим в списке select, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.
• Логическое описание и решение:
• Группировка по и NULL-значения:

Ошибка “Столбец недопустим в списке select, потому что он не содержится ни в агрегатной функции, ни в предложении GROUP BY” упомянутые ниже, возникают при выполнении «GROUP BY» запроса, и вы включили в список select хотя бы один столбец, который не является ни частью условия group by, ни содержится в агрегатной функции, например max(), min(), sum(), count() и avg(). Поэтому, чтобы заставить запрос работать, нам нужно добавить все неагрегированные столбцы либо в пункт group by, если это возможно и не влияет на результаты, либо включить эти столбцы в подходящую агрегатную функцию, и это будет работать как шарм. Ошибка возникает в MS SQL, но не в MySQL.

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

Group by clause:

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

Группировка по стратегии Split-Apply-Combine:

Группа по использует стратегию «split-apply-combine»

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

Это можно объяснить на примере, приведенном ниже. Сначала создайте базу данных с именем “appuals».

Пример:

Создайте таблицу «сотрудник«используя следующий код.

USE [appuals]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
УСТАНОВИТЬ ANSI_PADDING
GO
CREATE TABLE [dbo].[сотрудник](
[e_id] [int] NOT NULL,
[e_ename] [varchar](50) NULL,
[dep_id] [int] NULL,
[salary] [int] NULL,
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[e_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

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

Insert into employee(e_id,e_ename,dep_id,salary)
values(101,'Sadia',1,6000),
(102,'Saba',1,5000),
(103,'Sana',2,4000),
(104,'Hammad',2,3000),
(105,'Umer',3,4000),
(106,'Kanwal',3,2000)

Выходные данные будут выглядеть следующим образом.

Теперь выберите данные из таблицы, выполнив следующий оператор.

select * from employee

Вывод будет выглядеть следующим образом.

Теперь сгруппируем данные по таблице в соответствии с id отдела.

select dep_id, salary from employee group by dep_id

Ошибка: Column ’employee.sallary’ недопустим в списке select, потому что он не содержится ни в агрегатной функции, ни в предложении GROUP BY.

Указанная выше ошибка возникает из-за того, что выполняется запрос “GROUP BY” и вы включили “employee.столбец salary” в списке select, который не является частью пункта group by и не включен в агрегатную функцию.

Решение:

Как мы знаем, что “group by” возвращает одну строку, поэтому нам нужно применить агрегатную функцию к столбцам, не используемым в предложении group by, чтобы избежать этой ошибки. Наконец, примените функцию group by и агрегатную функцию, чтобы найти среднюю зарплату сотрудника в каждом отделе, выполнив следующий код.

select dep_id,avg(salary) as average_sallary from employee group by dep_id

Более того, если мы изобразим эту таблицу в соответствии со структурой split_apply_combine, то она будет выглядеть следующим образом.

На рисунке выше показано, что сначала таблица группируется на три группы по id отдела, затем применяется агрегатная функция avg() для нахождения среднего значения зарплаты, которое затем объединяется с id отдела. Таким образом, таблица группируется по id отдела, а зарплата агрегируется по отделам.

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

Логическое описание использования функций group by и aggregate вместе:

Теперь мы разберем использование “group by” и “aggregate functions” логически на примере.

Создайте таблицу с именем “люди” в базе данных с помощью следующего кода.

USE [appuals]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[people](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[имя] [varchar](500) NULL,
[город] [varchar](500) NULL,
[state] [varchar](500) NULL,
[возраст] [int] NULL
) ON [PRIMARY]
GO

Теперь вставьте данные в таблицу с помощью следующего запроса.

insert into people(name, city, state, age)
значения
('Meggs', 'MONTEREY','CA',20),
('Staton','HAYWARD','CA',22),
('Irons', 'IRVINE' ,'CA',25)
('Krank', 'PLEASANT', 'IA',23),
('Davidson','WEST BURLINGTON','IA',40),
('Pepewachtel' ,'FAIRFIELD' ,'IA',35)
('Schmid', 'HILLSBORO', 'OR', 23),
('Davidson' ,'CLACKAMAS','OR',40),
('Condy','GRESHAM','OR',35)

Вывод будет выглядеть следующим образом:

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

select age, count(*) as no_of_residents from people group by state

Ошибка: Колонка ‘люди.age’ недопустим в списке select, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.

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

“Msg 8120, Level 16, State 1, Line 16 Column ‘people.возраст’ недопустим в списке select, потому что он не содержится ни в агрегатной функции, ни в предложении GROUP BY”.

Эта ошибка возникает потому, что “GROUP BY” Запрос выполнен, и вы включили “‘люди. age” столбец в списке select, который не является ни частью условия group by, ни включен в агрегатную функцию.

Группировка по штату приводит к ошибке

Логическое описание и решение:

Это не синтаксическая ошибка, а логическая ошибка. Как мы видим, столбец «no_of_residents” возвращает только одну строку, теперь как мы можем вернуть возраст всех жителей в одном столбце? Мы можем получить список возраста людей, разделенных запятыми, или средний возраст, минимальный или максимальный возраст. Таким образом, нам нужна дополнительная информация о столбце “возраст”. Мы должны количественно определить, что мы имеем в виду под столбцом «возраст». По возрасту, что мы хотим получить. Теперь мы можем изменить наш вопрос, добавив более конкретную информацию о столбце «возраст» следующим образом.

Найти количество жителей и средний возраст жителей в каждом штате. Учитывая это, мы должны изменить наш запрос, как показано ниже.

select state,avg(age) as Age,count(*) as no_of_residents from people group by state

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

Поэтому очень важно логически продумать, что возвращать в операторе select.

Кроме того, необходимо учитывать следующие моменты используя “group by”, чтобы избежать ошибок.

Группировка по и NULL-значения:

Сначала добавьте еще одну строку в таблицу с именем «people» со столбцом “state” как empty/null.

insert into people(name, city, state,age) values ('Kanwal' ,'GRESHAM' ,'',35)

Теперь выполните следующий оператор.

select state,avg(age) as Age,count(*) as no_of_residents from people group by state

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

Теперь увеличим количество нулевых строк, вставив больше строк в таблицу с null в качестве состояния.

insert into people(имя, город, штат, возраст)
значения ('Kanwal','IRVINE','NULL',35), ('Krank','PLEASANT','NULL',23)

Теперь снова выполните тот же запрос, чтобы выбрать результат. Набор результатов будет выглядеть следующим образом.

На этом рисунке видно, что пустой столбец рассматривается как отдельная группа, а нулевой столбец с 2 строками рассматривается как другая отдельная группа с двумя количествами жителей. Вот как работает “группа по”.