MySQL Forums
Forum List  »  Newbie

Max in group with totals and zeroes
Posted by: Wim Roffel
Date: November 28, 2018 06:40AM

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?

Options: ReplyQuote


Subject
Written By
Posted
Max in group with totals and zeroes
November 28, 2018 06:40AM


Sorry, you can't reply to this topic. It has been closed.

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.