Tables in the database

Customers:

cust_id number(10)|cust_name varchar2(50)|email varchar2(50)|order_sum varchar2(50)|id_firms number(10)

Firms:

 f_name varchar2(50)|f_id number(10)|itme_cooperation timestamp|email varchar2(50)

Orders:

 order_id number(10)|order_sum number(10)|oder_date timestamp|customers_id number(10)

I need to choose customers with the sum of their orders and the names of the companies for which the most orders were made. This can be obtained only through customers.id_firms, as far as I understand.

That's how I display the names of customers (customers) and the amount of their orders.

 select customers.CUST_NAME, sum(orders.order_sum) sum_of_orders from customers, orders group by customers.CUST_NAME,customers.CUST_ID, orders.customers_id having customers.CUST_ID = orders.CUSTOMERS_ID ORDER BY customers.CUST_NAME;

But how can we also add a sample of the maximum number of customers - firms' links here, to show for which firms this customer has the largest number of orders?

The result should be:

 ИмСна Ρ„ΠΈΡ€ΠΌ | ИмСна Π·Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊΠΎΠ² | Π‘ΡƒΠΌΠΌΠ° Π·Π°ΠΊΠ°Π·ΠΎΠ² (общая для Π·Π°ΠΊΠ°Π·Ρ‡ΠΈΠΊΠ°, Π° Π½Π΅ для ΠΊΠΎΠ½ΠΊΡ€Π΅Ρ‚Π½ΠΎΠΉ Ρ„ΠΈΡ€ΠΌΡ‹ Π² ΠΏΠ΅Ρ€Π²ΠΎΠΌ столбцС)

    1 answer 1

    Do not write connection conditions in the HAVING clause. Oracle is of course smart and will understand everything (it will check the conditions before the grouping), but HAVING is absolutely not done for this. At least in WHERE, I transferred to JOIN.

    Secondly, the customer refers to your company. Those. one customer has one ONE company, which means that we choose "any" in the group with the 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;

    Have you accurately specified the scheme? Logically, the firm should refer to the Orders table. and then the problem is solved more interesting. With additional intermediate grouping.

     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 several firms have the maximum number, this query will find only one of them. You can of course list them separated by commas, but I don’t see any sense in this business. Although if necessary, I can add.

    • Thanks for clarifying! - TehD
    • I added a link to the firm in Orders, but Oracle SQL Developer scolds for a query with an intermediate grouping: ORA-00907: missing right parenting 00907. 00000 - "missing right parenthesis" * Cause: * Action: Error at Line: 124 Column: 59 - TehD
    • @TehD, there is a typo. CASW instead of CASE. Corrected. - pegoopik
    • ... really) I did not notice, although I looked at close range. ))) Thank! - TehD