There is a query in the database that I can not properly formulate (that would work adequately). There is an orders table in which there are customers id, and there are two more tables: customers and firms for which an order was made (mtm through another groups table, where there are id companies and customers). I need to select customers who have the largest number of orders and related firms and sort them by the number of (orders) in descending order ... while I struggle unsuccessfully. Can someone suggest?

Orders
| id | sum | date | customer_id |

Customers
| id | name | last_name | e_mail |

Firms
| id | name | e_mail |

Groups
| customer_id | firm_id |

The output should be:
Firms.name Customers.name COUNT (the number of orders given by Customers.name)

Ie, this client has the largest number of orders in general and the name of the company with which the largest number of links in Groups is coming, because as a single client, it can order for different companies and belong to different companies.

  • 2
    Has anyone understood from the question the structure of the database of the questioner? - Visman
  • if I understand correctly, the last table - groups - is a mapping (N to N) between customer and firms - MaxU
  • Give the question of exactly which SQL dialect is used. Specify the exact structure of all tables, the query that you have already received and what you want to get at the output (the difference between the output of your query from the required one) - Mike

1 answer 1

You can try something like this:

 select c.c_id as "customer id", ord.cnt as "orders count", frm.cnt as "firms count" from c left outer join (select c_id, count(*) as cnt from o group by c_id) ord on c.c_id = ord.c_id left outer join (select c_id, count(*) as cnt from g group by c_id) frm on c.c_id = frm.c_id order by ord.cnt desc nulls last, frm.cnt desc nulls last

where c, o, g - tables with customers, orders and customer relationships with firms ( groups )

  • Thank you, but not quite, added structure to the question. - TehD pm
  • @TehD And what DB? - Konstantin Sorokin
  • postgresql worth it - TehD
  • @TehD Do I understand correctly that there are no duplicates in groups ? Those. if the customer is associated with the company, then for this connection there will be only one entry in the table? - Konstantin Sorokin
  • Yes, all entries (in two columns) are unique. - TehD