The "max in group" is discussed many times on the internet. For example:
https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql
However, I want it with a few twists and I can't find the right way.
I have a list of customers and I want to see for each of them the sum of all orders and the latest order.
I now have the following query:
SELECT c.*,o.*,o3.* FROM customer c
LEFT JOIN orders o ON o.id_customer=c.id_customer
INNER JOIN (SELECT MAX(o2.id_order) AS maxorder, SUM(o2.total_paid) AS sales
FROM orders o2 WHERE o2.valid='1' GROUP BY o2.id_customer) o3
ON (o.id_order=o3.maxorder) ORDER BY c.id_customer
This does most of what I want. However, I don't see the customers without an order - what I do want. I suspect the INNER JOIN is somehow causing the issue but I don't know how to solve this.
Can someone help me?