query to get customer list using JOIN with sum () of the amounts spent in orders

Question

I have the following tables

table anag (customer registry)

---

  id  |  surname   |  name  |     phone      | 
----------------------------------------------
  1   |  Brown     |  Jack  |  +3989265781   |
  2   |  Smith     |  Bill  |  +3954872358   |
  3   |  Rogers    |  Stan  |  +3912568453   |
  4   |  Pickford  |  Eric  |  +3948521358   |
----------------------------------------------

table levels (table that connects each customer to his salesperson. For database registration reasons, the link between customer and seller is given by the customer's telephone number)

---

  id  |  client_phone  |  id_seller  |
--------------------------------------
  1   |  +3989265781   |     4       | 
  2   |  +3954872358   |     7       |
  3   |  +3912568453   |     7       |
  4   |  +3948521358   |     8       |
--------------------------------------

table orders (contains all purchases made by customers, of course)

---

  id  |  id_client  |  id_item  |  id_seller  |  price  |  status  |
--------------------------------------------------------------------
  1   |      1      |     2     |      4      |  12.50  |    2     |
  2   |      2      |     2     |      7      |  12.50  |    2     |
  3   |      2      |     3     |      7      |  10.00  |    3     |
  4   |      2      |     3     |      7      |  10.00  |    3     |
  5   |      2      |     4     |      7      |  20.50  |    1     |
  6   |      3      |     2     |      7      |  12.50  |    1     |
  7   |      3      |     5     |      7      |  19.00  |    3     |
  8   |      3      |     7     |      7      |  31.00  |    2     |
  9   |      4      |     1     |      8      |   5.00  |    1     |
--------------------------------------------------------------------

What I'm trying to do is get from the JOIN of these tables a complete list by seller of his customers sorted in descending order by the amount spent on orders as long as the order status is 2 or 3
Something like this (example seller id 7):

---

  id  |  surname  |  name  |  amaount  | 
----------------------------------------
  3   |  Rogers   |  Stan  |  50.00    |
  2   |  Smith    |  Bill  |  32.50    |
----------------------------------------

I have tried with this query which seems correct to me, but unfortunately it returns me error in fetch_assoc()

SELECT a.id, a.surname, a.name, o.amount FROM levels AS l
JOIN anag AS a ON a.phone = l.client_phone
JOIN {
    SELECT id_client, SUM(price) AS amount FROM orders
    WHERE id_seller = '7' AND (status = '2' OR status = '3') GROUP BY id_client
} AS o ON o.id_client = a.id
WHERE l.id_seller = '7'
ORDER BY o.amount DESC

If I separate the subquery from the main query, both return the data I expect and it seems strange to me the JOIN between the two does not work properly

Answer 1

I think the only real error is using curly braces instead of parentheses:

SELECT a.id, a.surname, a.name, o.amount
FROM levels l JOIN
     anag a
     ON a.phone = l.client_phone JOIN
     (SELECT id_client, SUM(price) AS amount
      FROM orders
      WHERE id_seller = '7' AND status IN ('2', '3'))
      GROUP BY id_client
     ) o
     ON o.id_client = a.id
WHERE l.id_seller = '7'
ORDER BY o.amount DESC;

In addition:

• You can use IN to shorten an equality comparison to multiple values.
• Although I left them in, status and id_seller look like numbers. If so, drop the single quotes. Don't mix data types.
• Your question is ambiguous on what to do if the seller in orders differs from the seller in anag for a customer. This keeps your logic (the sellers need to match).

Answer 2

SELECT a.id, a.surname, a.name, sum(o.price) 'amount'

FROM anag a

LEFT JOIN levels l ON l.id =a.id

LEFT JOIN orders of ON o.id_seller = l.id_seller AND o.id_client = l.id

GROUP BY o.id_seller

ORDER BY amount DESC