I'm new to SQL. There is a request that I have been fighting for a day, and I don’t understand how to approach it, although, probably, it’s not that difficult.

The bottom line:

There are tables:

- companies (id, name),
- customers (id, name),
- projects (id, name, cost),

as well as companies_projects (company_id, project_id) and customers_projects (customer_id, project_id). Communication ManyToMany .
In addition, there are more tables, but they do not participate in this query.

It is necessary to find a client for each company that brings this company the lowest profit. That is, it turns out, the sum of cost projects of this client in the company is the smallest among the other clients who have projects in this company.
Print, respectively, companies.name, customers.name.

I would be grateful for your help, because I myself do not even understand in which direction to move.

  • How is the company connected with the client? - sterx
  • Through projects. Well, that is, there is no companies_customers table (company_id, customer_id). I don’t know, of course, how true this is, but I thought that such a connection would be unnecessary, because all the clients of the company can be learned on the basis of the projects that it leads. If this is the wrong approach, correct me. - Protagor

1 answer 1

in projects I would add customer_id company_id so it would be easier to do aggregation queries

nevertheless we use the SUM function

SELECT SUM (p.cost) as customer_agg_cost, cu.name as customer_name, co.name as company name FROM projects p LEFT JOIN customers_projects cup ON (p.id = cup.project_id) LEFT JOIN customers cu ON (cup.customer_id = cu .id) LEFT JOIN companies_projects cop ON (p.id = cop.project_id) LEFT JOIN companies co ON (cop.company_id = co.id) GROUP BY cup.customer_id ORDER BY customer_agg_cost

  • Unfortunately, the console swears: "ERROR: the column" cu.name "must appear in the GROUP BY clause or be used in an aggregate function." How to get around this? .. - Protagor
  • and so, too, as I understand the error? SELECT SUM (p.cost) as customer_agg_cost, cu.name as customer_name FROM projects p LEFT JOIN customers_projects cup ON (p.id = cup.project_id) LEFT JOIN customers cu ON (cup.customer_id = cu.id) GROUP BY cup. customer_id ORDER BY customer_agg_cost would be necessary specifically to know your example, my example for MySQL and it is working - sterx
  • Yes, the same thing: (I have PostgreSQL. - Protagor
  • as well as SELECT SUM (p.cost) as customer_agg_cost, cu.name FROM projects p LEFT JOIN customers_projects cup ON (p.id = cup.project_id) LEFT JOIN customers cu ON (cup.customer_id = cu.id) GROUP BY cu. name ORDER BY customer_agg_cost - sterx
  • So he just gives the amount of costs for each client. - Protagor 2:51 pm