I am looking for clarification on this. I am writing two queries below:
We have a table of employee name with columns ID , name , salary
1. Select name from employee
where sum(salary) > 1000 ;
2. Select name from employee
where substring_index(name,' ',1) = 'nishant' ;
Query 1 doesn’t work but Query 2 does work. From my development experience, I feel the possible explanation to this is:
The sum() works on a set of values specified in the argument. Here
‘salary’ column is passed , so it must add up all the values of this
column. But inside where clause, the records are checked one by one ,
like first record 1 is checked for the test and so on. Thus
sum(salary) will not be computed as it needs access to all the column
values and then only it will return a value.
Query 2 works as substring_index() works on a single value and hence here it works on the value supplied to it.
Can you please validate my understanding.
Cœur
36.9k25 gold badges193 silver badges262 bronze badges
asked Feb 26, 2017 at 16:10
15
The reason you can’t use SUM() in the WHERE clause is the order of evaluation of clauses.
FROM tells you where to read rows from. Right as rows are read from disk to memory, they are checked for the WHERE conditions. (Actually in many cases rows that fail the WHERE clause will not even be read from disk. «Conditions» are formally known as predicates and some predicates are used — by the query execution engine — to decide which rows are read from the base tables. These are called access predicates.) As you can see, the WHERE clause is applied to each row as it is presented to the engine.
On the other hand, aggregation is done only after all rows (that verify all the predicates) have been read.
Think about this: SUM() applies ONLY to the rows that satisfy the WHERE conditions. If you put SUM() in the WHERE clause, you are asking for circular logic. Does a new row pass the WHERE clause? How would I know? If it will pass, then I must include it in the SUM, but if not, it should not be included in the SUM. So how do I even evaluate the SUM condition?
answered Feb 26, 2017 at 16:53
2
Why can’t we use aggregate function in where clause
Aggregate functions work on sets of data. A WHERE clause doesn’t have access to entire set, but only to the row that it is currently working on.
You can of course use HAVING clause:
select name from employee
group by name having sum(salary) > 1000;
If you must use WHERE, you can use a subquery:
select name from (
select name, sum(salary) total_salary from employee
group by name
) t where total_salary > 1000;
answered Feb 26, 2017 at 16:40
Gurwinder SinghGurwinder Singh
38.3k6 gold badges51 silver badges76 bronze badges
0
sum() is an aggregation function. In general, you would expect it to work with group by. Hence, your first query is missing a group by. In a group by query, having is used for filtering after the aggregation:
Select name
from employee
group by name
having sum(salary) > 1000 ;
answered Feb 26, 2017 at 16:14
Gordon LinoffGordon Linoff
1.2m57 gold badges639 silver badges781 bronze badges
0
Using having works since the query goes direct to the rows in that column while where fails since the query keep looping back and forth whenever conditions is not met.
answered Mar 5, 2020 at 12:06
У меня возникла следующая странная проблема с запросом Sqlite в устройствах версии 4.4.2 на Android 4.4.
Структура таблицы следующая:
CREATE TABLE responsemaster (
id INTEGER PRIMARY KEY AUTOINCREMENT,
formid integer,
proposerid text,
fieldid integer,
response text,
remarks text,
listId integer,
isMarked integer
)
и запрос, который дает ошибку,
SELECT id as id, formid as formid, proposerid as proposerid, MIN(fieldid) fieldid, response as response, remarks as remarks, listId as listId, isMarked as isMarked
FROM responsemaster
WHERE formid = 40066 AND proposerid = '7ca6533a-c5f0-43e2-9980-83f9ae2c7370201802230113550000'
GROUP BY fieldid
Исключением я получаю следующее
android.database.sqlite.SQLiteException: aggregate functions are not allowed in the GROUP BY clause (code 1):,
while compiling: SELECT id as id, formid as formid, proposerid as proposerid, **MIN(CAST(fieldid AS INTEGER)) AS fieldid**, response as response, remarks as remarks, listId as listId, isMarked as isMarked FROM responsemaster WHERE formid = 40066 AND proposerid = '7ca6533a-c5f0-43e2-9980-83f9ae2c7370201802230113550000' GROUP BY fieldid
Вышеприведенный запрос будет успешно выполнен в браузере SQLite и в устройствах с расширенными версиями Android 4.4.2. Пожалуйста, может кто-нибудь помочь мне, как я могу исправить эту ошибку?
этот код sql выдает
агрегатные функции не допускаются в WHERE
SELECT o.ID , count(p.CAT)
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
WHERE count(p.CAT) > 3
GROUP BY o.ID;
Как избежать этой ошибки?
3 ответов
заменить WHERE п. с HAVING, например:
SELECT o.ID , count(p.CAT)
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
GROUP BY o.ID
HAVING count(p.CAT) > 3;
HAVING похож на WHERE, то есть оба используются для фильтрации результирующих записей, но HAVING используется для фильтрации агрегированных данных (при GROUP BY используется).
использовать HAVING пункт вместо WHERE
попробуйте это:
SELECT o.ID, COUNT(p.CAT) cnt
FROM Orders o
INNER JOIN Products p ON o.P_ID = p.P_ID
GROUP BY o.ID HAVING cnt > 3
будет ли self join идти на бросок с join, где у нас есть условие для перечисления цен, которые больше медианы цен, перечисленных в таблице order?
например. order_item, Order_Price
поскольку агрегатные функции нельзя использовать в предложении WHERE >
select a.order_item_product_price, count(distinct
a.order_item_product_price) from
default.order_items a , default.order_items b
where a.order_item_product_price = b.order_item_product_price
group by a.order_item_product_price
having a.order_item_product_price > appx_median(b.order_item_product_price)
order by a.order_item_product_price limit 10
У меня есть следующие данные:
table: identifier
objectid type name value
9345783451 GUID msisdn bc3b2286379da25fd6ef3bac120827589c783
9345783451 UMID umid f701a99c-96c9-4d4a-8508-932eeeaca77c
9345783451 UMDID umdid 2840f267-4bdd-4af4-8b36-72badbce1e11
9345783451 UMDID umdid bbe467f5-0fc4-4624-8b01-dd656767d3b2
9345783451 GUID encrypted ZPmBF2Spq8DBX0wl
9345783451 UMDID umdid a4c6b3cb-938f-4ae5-8246-f2612ffd946b
9345783451 UMDID umdid 2a8e5f98-5a74-431e-bbaf-7c75fdeb991a
9345783451 UMDID umdid 269a39b9-1122-4d08-9ca4-36f6c8e00e8e
9345783451 COOKIE amcv 39852788960115122553605179944081330813
9345783451 UMDID umdid 7715969f-63ab-4540-952c-73beafb46024
9345783451 GROUPID ban-sha2 1d98f855e9a4fbeba1937f774b6dbab2ca194b5
9345783451 COOKIE token 21agqB6x_H8.1575825731298.aXHr4GoVupopE
9345783451 GUID acn 12433792
9345783451 UMDID umdid f2cf7402-21d2-44ea-b432-66e997cfebbf
9345783451 GUID targetId 1255625699
9345783451 UMDID umdid b8d55cdd-4a95-4e07-8eeb-f281a0961a6a
9345783451 UMDID umdid af890608-b512-4a96-9274-f39f388ff442
9345783451 UMDID umdid 0c55ecb1-e24e-419d-97a1-48f6eba45fe0
Затем я делаю следующий запрос:
select objectid,
count(objectid) filter ( where type = 'GUID' ) as guid_count,
count(objectid) filter ( where type = 'GUID' and (name in
('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) ) as cuid_count,
count(objectid) filter ( where name = 'umdid' ) as umdid_count
from identifier
where objectid = '9345783451'
group by objectid;
Результат верный:
objectid guid_count cuid_count umdid_count
9345783451 4 3 10
Затем я добавляю два столбца, имя и значение:
select objectid,
count(objectid) filter ( where type = 'GUID' ) as guid_count,
count(objectid) filter ( where type = 'GUID' and (name in
('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) ) as cuid_count,
count(objectid) filter ( where name = 'umdid' ) as umdid_count,
case
when identifier.name = 'acn' then identifier.value::text
else '' end as acn
from identifier
where objectid = '9345783451'
group by objectid, name, value;
Это возвращение:
objectid guid_count cuid_count umdid_count acn
9345783451 0 0 1 ""
9345783451 1 0 0 ""
9345783451 0 0 1 ""
9345783451 0 0 0 ""
9345783451 0 0 1 ""
9345783451 1 1 0 12433792
9345783451 0 0 1 ""
9345783451 0 0 1 ""
9345783451 1 1 0 ""
9345783451 0 0 0 ""
9345783451 0 0 1 ""
9345783451 0 0 1 ""
9345783451 0 0 0 ""
9345783451 0 0 1 ""
9345783451 0 0 1 ""
9345783451 0 0 0 ""
9345783451 1 1 0 ""
9345783451 0 0 1 ""
"" empty string
Я ожидаю:
objectid guid_count cuid_count umdid_count acn
9345783451 4 3 10 12433792
Если я добавлю группу по guid_count, cuid_count и umdid_count:
[42803] ERROR: aggregate functions are not allowed in GROUP BY Position: 26
Как преодолеть эту проблему? Не могли бы вы указать мне, где моя ошибка?
Спасибо.
1 ответ
Лучший ответ
Вы должны использовать агрегат для добавленного столбца, например:
select objectid,
count(objectid) filter ( where type = 'GUID' ) as guid_count,
count(objectid) filter (
where type = 'GUID'
and (name in ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId'))
) as cuid_count,
count(objectid) filter ( where name = 'umdid' ) as umdid_count,
max(case
when identifier.name = 'acn' then identifier.value::text
else '' end) as acn
from identifier
where objectid = '9345783451'
group by objectid;
1
klin
16 Дек 2019 в 20:01
В SQL имеется множество предопределенных агрегатных функций, которые можно использовать для написания запросов для получения именно такой информации. Предложение GROUP BY указывает, как группировать строки из таблицы данных при агрегировании информации, тогда как предложение HAVING отфильтровывает строки, которые не принадлежат указанные группы.
Агрегатные функции выполняют различные действия, такие как подсчет всех строк в таблице, усреднение данных столбца и суммирование числовых данных. Агрегаты также могут искать в таблице, чтобы найти самые высокие значения “MAX” или самые низкие значения “MIN” в столбце. Как и в случае других типов запросов, вы можете ограничить или отфильтровать строки, с которыми работают эти функции, с помощью предложения WHERE. Например, если менеджеру необходимо знать, сколько сотрудников работает в организации, для получения этой информации можно использовать агрегатную функцию COUNT (*). Функция COUNT (*), показанная в приведенном ниже операторе SELECT, подсчитывает все строки в Таблица.
SELECT COUNT(*) FROM employees; COUNT(*) ---------- 24
Таблица результатов для функции COUNT (*) представляет собой один столбец из одной строки, известный как скалярный результат или значение. Обратите внимание, что таблица результатов имеет заголовок столбца, который соответствует имени агрегатной функции, указанной в предложении SELECT.
Некоторые из часто используемых агрегатных функций приведены ниже:
SUM( [ALL | DISTINCT] expression ) AVG( [ALL | DISTINCT] expression ) COUNT( [ALL | DISTINCT] expression ) COUNT(*) MAX(expression) MIN(expression)
Ключевые слова ALL и DISTINCT являются необязательными и выполняются так же, как и с предложениями SELECT, которые вы научились писать. Ключевое слово ALL является значением по умолчанию, где эта опция разрешена. Выражение, указанное в синтаксисе, может быть константой, функцией, или любая комбинация имен столбцов, констант и функций, связанных арифметическими операторами. Однако агрегатные функции чаще всего используются с именем столбца. За исключением функции COUNT, все агрегатные функции не учитывают значения NULL.
Есть два правила, которые вы должны понимать и соблюдать при использовании агрегатов:
-
Агрегатные функции могут использоваться как в предложениях SELECT, так и в предложениях HAVING (предложение HAVING будет описано далее в этой главе).
-
Агрегатные функции нельзя использовать в предложении WHERE. Его нарушение приведет к тому, что групповая функция Oracle ORA-00934 не допускает здесь сообщения об ошибке.
Агрегатные функции могут использоваться как в предложениях SELECT, так и в предложениях HAVING (предложение HAVING будет описано далее в этой главе).
Агрегатные функции нельзя использовать в предложении WHERE. Его нарушение приведет к тому, что групповая функция Oracle ORA-00934 не допускает здесь сообщения об ошибке.
иллюстрации
Приведенный ниже запрос SELECT подсчитывает количество сотрудников в организации.
SELECT COUNT(*) Count FROM employees; COUNT ----- 24
Приведенный ниже запрос SELECT возвращает среднюю зарплату сотрудников в организации.
SELECT AVG(Salary) average_sal FROM employees; AVERAGE_SAL ----------- 15694
Приведенный ниже запрос SELECT возвращает сумму зарплат сотрудников в организации.
SELECT SUM(Salary) total_sal FROM employees; TOTAL_SAL --------- 87472
Приведенный ниже запрос SELECT возвращает самые старые и самые последние даты приема сотрудников в организации.
SELECT MIN (hire_date) oldest, MAX (hire_date) latest FROM employees; OLDEST LATEST --------- ----------- 16-JAN-83 01-JUL-2012
ГРУППА ПО
Агрегатные функции обычно используются вместе с предложением GROUP BY. Предложение GROUP BY позволяет использовать агрегатные функции для ответа на более сложные вопросы управления, такие как:
Какова средняя зарплата сотрудников в каждом отделе?
Сколько сотрудников работает в каждом отделе?
Сколько сотрудников работает над конкретным проектом?
Группировка за функцией устанавливает группы данных на основе столбцов и объединяет информацию только внутри группы. Критерий группировки определяется столбцами, указанными в предложении GROUP BY. Следуя этой иерархии, данные сначала организуются в группы, а затем предложение WHERE ограничивает строки в каждой группе.
Правила использования предложения GROUP BY
(1) Все зависимые столбцы или столбцы, используемые в функции GROUP BY, должны формировать основу группировки, следовательно, они также должны быть включены в предложение GROUP BY.
SELECT DEPARTMENT_ID, SUM(SALARY) FROM employees; DEPARTMENT_ID, * ERROR at line 2: ORA-00937: not a single-group group function
(2) Предложение GROUP BY поддерживает не псевдоним столбца, а фактические имена.
(3) Предложение GROUP BY может использоваться только с агрегатными функциями, такими как SUM, AVG, COUNT, MAX и MIN. Если оно используется с однорядными функциями, Oracle выдает исключение как «ORA-00979: не выражение GROUP BY» ,
(4) Агрегатные функции нельзя использовать в предложении GROUP BY. Oracle вернет сообщение об ошибке «ORA-00934: групповая функция не разрешена».
Ниже запроса перечислены количество сотрудников, работающих в каждом отделе.
SELECT DEPARTMENT_ID, COUNT (*) FROM employees GROUP BY DEPARTMENT_ID;
Аналогичным образом, ниже запрос, чтобы найти сумму зарплат для соответствующих идентификаторов работы в каждом отделе. Обратите внимание, что группа создана на основе отдела и идентификатора работы. Таким образом, они появляются в предложении GROUP BY.
SELECT DEPARTMENT_ID, JOB_ID, SUM (SAL) FROM employees GROUP BY DEPARTMENT_ID, JOB_ID;
Приведенный ниже запрос также дает тот же результат. Обратите внимание, что группировка основана на столбцах идентификатора отдела и идентификатора задания, но не используется для отображения.
SELECT SUM (SALARY) FROM employees GROUP BY DEPARTMENT_ID, JOB_ID;
Использование DISTINCT, ВСЕХ ключевых слов с агрегатными функциями
Задав ключевое слово DISTINCT во входном параметре, функция group by учитывает только уникальное значение столбца для агрегации. Указывая ключевое слово ALL с входным параметром, group by function учитывает все значения столбца для агрегации, включая нули и дубликаты. ALL является спецификацией по умолчанию.
Пункт HAVING
Предложение HAVING используется для агрегатных функций так же, как предложение WHERE используется для имен столбцов и выражений. По существу, предложения HAVING и WHERE выполняют то же самое, то есть фильтруют строки из включения в таблицу результатов на основе условия. , Хотя может показаться, что предложение HAVING отфильтровывает группы, это не так. Скорее, предложение HAVING фильтрует строки.
Когда все строки для группы удаляются, так же как и для группы. Подводя итог, можно отметить важные различия между предложениями WHERE и HAVING:
Предложение WHERE используется для фильтрации строк перед действием GROUPING (т. Е. Перед вычислением агрегатных функций).
Предложение HAVING фильтрует строки ПОСЛЕ действия GROUPING (т. Е. После вычисления агрегатных функций).
SELECT JOB_ID, SUM (SALARY) FROM employees GROUP BY JOB_ID HAVING SUM (SALARY) > 10000;
Предложение HAVING является условным параметром, который непосредственно связан с параметром предложения GROUP BY, поскольку предложение HAVING удаляет строки из таблицы результатов на основе результата предложения GROUP BY.