PHP DM Gate v0.9 » Помогите оптимизировать SQL запрос <span class="vd">[FireBird 2.5]</span>

_DATA_ (24.01.16 22:40) [0]

Доброго времени суток! Пожалуйста помогите оптимизировать SQL запрос, выполняется довольно долго ... (

SELECT DISTINCT(GROUP_ID_)"Вн.Номер",
(SELECT FIRST 1 GROUP_NAME_ FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)"Заявка",
(SELECT FIRST 1 ADD_TIME_ FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)"Принята",
(SELECT FIRST 1 DATE_OF_START_AUKCION_ FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)"Дата торгов",
(SELECT COUNT(*) FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)"Кол-во позиций",
(SELECT COUNT(*) FROM files WHERE GROUP_ID_ = MAIN.GROUP_ID_)"Файлов",
(case when (SELECT FIRST 1 ON_SITE_ FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)=1 then 'да' else 'нет' end)"Размещение",
(case when (SELECT FIRST 1 IS_COMPLEATE_ FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)=1 then 'да' else 'нет' end)"Завершен",
(SELECT FIRST 1 LOT_NUM_ FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)"Лот", (SELECT sum(SUMMA_BEZ_NDS_) FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)"Общая сумма",
(SELECT FIRST 1 NUM_FAILA_ZAYAVKI_ FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)"N заявки",

(SELECT FIRST 1 PARENT_LOGIN_ FROM ORDERS WHERE GROUP_ID_ = MAIN.GROUP_ID_)"От",
(case when (SELECT count(*) FROM MESSAGES M WHERE (GROUP_ID_ = MAIN.GROUP_ID_ and PARENT_LOGIN_ <> '55')
and time_ > coalesce((select LAST_SEEN_TIME_ FROM messages_seen_info WHERE GROUP_ID_ = M.group_id_ and PARENT_LOGIN_ = '55'),
current_timestamp-1000))=0 then '' else (SELECT count(*) FROM MESSAGES M WHERE (GROUP_ID_ = MAIN.GROUP_ID_ and PARENT_LOGIN_ <> '55')
and time_ > coalesce((select LAST_SEEN_TIME_ FROM messages_seen_info WHERE GROUP_ID_ = M.group_id_ and PARENT_LOGIN_ = '55'),current_timestamp-1000))
end) "Сообщ."

FROM ORDERS MAIN
ORDER BY 7 DESC,3 DESC

<Цитата>
Кщд © (25.01.16 07:05) [1]

group by

<Цитата>
_DATA_ (25.01.16 14:11) [2]

SELECT DISTINCT(o.GROUP_ID_)"Вн.Номер",
o.GROUP_NAME_"Заявка",
o.ADD_TIME_"Принята",
o.DATE_OF_START_AUKCION_"Дата торгов",
(SELECT COUNT(*) FROM ORDERS WHERE GROUP_ID_ = o.GROUP_ID_)"Кол-во позиций",
(SELECT COUNT(*) FROM files WHERE GROUP_ID_ = o.GROUP_ID_)"Файлов",
(case when o.ON_SITE_=1 then 'да' else 'нет' end)"Размещение",
(case when o.IS_COMPLEATE_=1 then 'да' else 'нет' end)"Завершен",
o.LOT_NUM_"Лот",
(SELECT sum(SUMMA_BEZ_NDS_) FROM ORDERS WHERE GROUP_ID_ = o.GROUP_ID_)"Общая сумма",
(SELECT FIRST 1 NUM_FAILA_ZAYAVKI_ FROM ORDERS WHERE GROUP_ID_ = o.GROUP_ID_)"N заявки",

o.PARENT_LOGIN_"От",

(case when (SELECT count(*) FROM MESSAGES M WHERE (GROUP_ID_ = o.GROUP_ID_ and PARENT_LOGIN_ <> '55')
and time_ > coalesce((select LAST_SEEN_TIME_ FROM messages_seen_info WHERE GROUP_ID_ = M.group_id_ and PARENT_LOGIN_ = '55'),
current_timestamp-1000))=0 then '' else (SELECT count(*) FROM MESSAGES M WHERE (GROUP_ID_ = o.GROUP_ID_ and PARENT_LOGIN_ <> '55')
and time_ > coalesce((select LAST_SEEN_TIME_ FROM messages_seen_info WHERE GROUP_ID_ = M.group_id_ and PARENT_LOGIN_ = '55'),current_timestamp-1000))
end) "Сообщ."

FROM ORDERS o
group by 1
ORDER BY 7 DESC,3 DESC

Выдает ошибку
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

И не понимаю как без под запросов вывести count(*) orders и count(*) files

<Цитата>
Кщд © (25.01.16 14:54) [3]

для того, чтобы решить вашу задачу, необходимо группировать по GROUP_ID_

что нужно сделать:
либо прочитать букварь по SQL
либо привыкать платить тому, кто его прочитал

даю на водку - по бедности
ID групп и кол-во позиций можно вывести так:
select group_id_, count(1) from orders group by group_id_)

<Цитата>