O
It is not necessary to describe the conditions of the connection in the HAVING proposal.
Oracle, of course, is smart, and it's gonna, uh, catch up on the conditions before the grouping, but HAVING's not done for that.
At least in WHERE, I moved to JOIN.Second, you're referring to the firm as a customer. I mean, one of the E.D.'s customers has a firm, which means they're picking "any" in the group's MAX function. select customers.CUST_NAME, MAX(firms.f_name)f_name, sum(orders.order_sum) sum_of_orders
from customers
INNER JOIN orders ON customers.CUST_ID = orders.CUSTOMERS_ID
INNER JOIN firms ON firms.f_id = customers.id_firms
group by customers.CUST_NAME,customers.CUST_ID
ORDER BY customers.CUST_NAME;
Are you sure you're right about the scheme?
In logic, the firm should be referred to in the Orders table, and then the task is more interesting. With an additional intermediary group.select CUST_NAME, SUM(sum_of_orders)sum_of_orders,
--выбираем имя фирмы с максимальным количеством заказов
MAX(CASE WHEN cnt_firm=cnt_max THEN f_name END)f_name_max_cnt
from(
select customers.CUST_NAME, customers.CUST_ID, firms.f_name,
sum(orders.order_sum) sum_of_orders,
--подсчитываем количество заказов для каждой фирмы, и максимальное количество
count(*)as cnt_firm, max(count(*))over(partition by customers.CUST_ID)cnt_max
from customers
INNER JOIN orders ON customers.CUST_ID = orders.CUSTOMERS_ID
INNER JOIN firms ON firms.f_id = orders.id_firms
group by customers.CUST_NAME, customers.CUST_ID, firms.f_id, firms.f_name
)T
group by CUST_NAME, CUST_ID
If a few firms have as many as possible, only one of them will this request.
You can enumerate them through the comma, but I don't see business sense.
If you need to, I can add it.