Pier Pier - 4 months ago 11
PHP Question

SQL - How to SUM and use WHERE clause on this SUM using 4 tables?

So I need to build a tool wich sorts my customers on several parameters and then export it to CSV.
Everything's working fine except my SQL query (I've always been bad at it).

Here is my DB structure:


  • Customers (id, CustomerLogin, CustomerNom, CustomerPrenom, CustomerReference)

  • Orders (OrderId, OrderTotal)

  • Products (ProductId, category)

  • OrderProduct (CPId, OrderId, ProductId)



I need to sort my customers to build a mailing list (CustomerLogin is a mail address).
Filters I need to implement:


  • Orders number

  • Orders total

  • Product Category



Here's my current query(using some php variables for total and categories):

SELECT C.id, C.CustomerReference, C.CustomerNom, C.CustomerPrenom, C.CustomerLogin, O.OrderTotal, O.OrderId
FROM customers C
JOIN orders O
ON C.id = O.CustomerId
JOIN OrderProduct OP
ON O.OrderId = OP.OrderId
JOIN products P
ON OP.ProduitId = P.ProduitId
WHERE O.OrderTotal >= $total
AND P.category IN($product_categories);


So this returns me datas of every customer entry in the order table where these orders are filtered by total and product category.
For example:

C.Id, C.ref, C.nom, C.prenom, C.login, O.OrderTotal, O.Id
1 - REF1 - DOE - John - johndoe@gmail.com - 550 - 1
1 - REF1 - DOE - John - johndoe@gmail.com - 150 - 4
1 - REF1 - DOE - John - johndoe@gmail.com - 800 - 8
5 - REF5 - BOND - James - jb@mi6.com - 007 - 25
8 - REF8 - ANOTHER - Dude - adude@gmail.com - 50 - 12


What I'd like to get:

C.Id, C.ref, C.nom, C.prenom, C.login, OrdersTotal, OrderNumber
1 - REF1 - DOE - John - johndoe@gmail.com - 1500 - 3
5 - REF5 - BOND - James - jb@mi6.com - 007 - 1
8 - REF8 - ANOTHER - Dude - adude@gmail.com - 50 - 1


Problem is I also need to filter OrdersTotal and OrderNumber, so how can I sum and display it in my "SELECT" and then filter it in the "WHERE"? Is that even possible? I thougt about using subqueries or other stuff but I'm pretty bad at it so I could use some help on this.

Sorry for that long question but I need to be precise on this!
Hope you guys will understand what I mean and be able to help me, thanks !

ps: I've translated my table and column names so you can understand, there might be some errors but it's working on my side.

UPDATE

Thanks to Surajit Biswas this is almost the solution:

SELECT * FROM(
SELECT C.id, C.CustomerReference, C.CustomerNom, C.CustomerPrenom, C.CustomerLogin, SUM(O.OrderTotal) order_total
FROM customers C
JOIN orders O ON C.id = O.CustomerId
JOIN OrderProduct OP ON O.OrderId = OP.OrderId
JOIN products P ON OP.ProduitId = P.ProduitId
WHERE P.category IN($product_categories)
GROUP BY C.id, C.CustomerReference, C.CustomerNom, C.CustomerPrenom, C.CustomerLogin
)A WHERE A.order_total >= $total


Now I need modify the WHERE clause so I can filter results on the orders count of each customer.

Answer

I would have used something kind of this:

SELECT OrderID, sum(Quantity) as sumquantity
FROM OrderDetails
group by OrderID

When my table structure is like:

OrderDetailID   OrderID ProductID   Quantity
1   10248   11  12
2   10248   42  10
3   10248   72  5
4   10249   14  9
5   10249   51  40
6   10250   41  10
7   10250   51  35

It gives me the output as:

ord_id  sumquantity
10248   27
10249   49
10250   60
10251   41

In fact at your case also it is easy...

SELECT C.id, C.CustomerReference, C.CustomerNom, C.CustomerPrenom, C.CustomerLogin,
      SUM(O.OrderTotal) order_total
FROM customers C  
  JOIN orders O  ON C.id = O.CustomerId  
  JOIN OrderProduct OP ON O.OrderId = OP.OrderId  
  JOIN products P ON OP.ProduitId = P.ProduitId
WHERE O.OrderTotal >= $total  
  AND P.category IN($product_categories)
GROUP BY C.id, C.CustomerReference, C.CustomerNom, C.CustomerPrenom, C.CustomerLogin