MySQL Forums
Forum List  »  Newbie

complex mysql query
Posted by: Moez Ben Rebah
Date: April 16, 2022 10:08PM

Hi guys, I need your help to get the appropriate query in order to retrieve the desired data table from my MySQL DB:
Clients table (that I want to retrieve):
+----------+------------------+----------+
| Name | Contracts_number | TOTAL € |
+----------+------------------+----------+
| Alpharis | 3 | 3000.00 |
| Granotis | 2 | 8000.00 |
| Kfe | 3 | 500.00 |
| Melangea | 5 | 2000.00 |
| Zintox | 5 | 35000.00 |
+----------+------------------+----------+

from these 3 tables:

Companies tables:
+----+-------------+------------------------------+-------+-------+
| id | name | adresse | cp | Town |
+----+-------------+------------------------------+-------+-------+
| 1 | Alpharis | 13, rue des bains | 38190 | Bernin|
| 2 | Melangea | 788 champs de mars | 69000 | Lyon |
| 3 | Kfe | 65, allée des champignons | 13022 | Marsei|
| 4 | Granotis | 87, park des alentours | 75001 | Paris |
| 5 | Zintox | 1, av des grandes terres | 42002 | StEnne|
| 6 | Duralium | 9541 grande rue | 38660 | Touvet|
| 7 | Vis mook | 9, chemin des fleurs | 75003 | PARIS |
| 8 | Data herbal | 38 résidence du Vercors | 69007 | LYON |
+----+-------------+------------------------------+-------+-------+

Contracts table:
+----+-------------+--------------+---------------------+---------+
| id | societes_id | ref | date | montant |
+----+-------------+--------------+---------------------+---------+
| 1 | 1 | CT20020512 | 2021-12-15 13:03:45 | 150000 |
| 2 | 8 | CR50012548 | 2021-01-04 13:03:45 | 500000 |
| 3 | 6 | CT5124795574 | 2022-02-13 13:04:41 | 840000 |
| 4 | 5 | CR45876254 | 2020-06-09 13:05:01 | 152432 |
| 5 | 2 | CT521425632 | 2019-06-06 00:00:00 | 1245896 |
+----+-------------+--------------+---------------------+---------+

and Contracts machines:
+----+-------------+-------------+--------+
| id | contracts_id| machines_id | numbre |
+----+-------------+-------------+--------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 3 | 5 |
| 3 | 2 | 5 | 2 |
| 4 | 2 | 4 | 3 |
| 5 | 3 | 7 | 3 |
| 6 | 3 | 2 | 10 |
| 7 | 4 | 3 | 1 |
| 8 | 5 | 4 | 4 |
| 9 | 5 | 1 | 5 |
| 10 | 5 | 6 | 2 |
+----+-------------+-------------+--------+

I tried this query but it's still not enough:

"SELECT s.nom, count(c.id) as contracts_number, sum(c.montant) as total FROM societees AS s JOIN contrats AS c ON s.id = c.societes_id GROUP BY s.nom;"

I need to include the contract machine table in order to multiply the number of the contracts by the montant in the contract table to get the exact total in the client table, please help me

Options: ReplyQuote


Subject
Written By
Posted
complex mysql query
April 16, 2022 10:08PM
April 17, 2022 11:44AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.