GROUP BY, but backwards

An odd question here.  I can't for the life of me figure out how to do this query.  I think it involves self-joins or something.  I'd like an orders table sorted by the customers who spend the most money first. The catch? I still want to see each order.  A sample table and desired output query can be found here:

http://pastebin.com/611949

So,

Sort by customer who spends the most money first.
Sort by each customer's *individual* order total second.

Customer 2 has spent the most money with us ($1938),
so we list his orders first, in descending order.

To be honest, it seems a little pointless, but sometimes that's just how people want the data.  Oh, and I would like to not have to use stored procs, because I may have to use this in another DB at some point.

Thanks,
-Brian
LVL 4
BrianPap22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mherchlCommented:
which version are you using?

CREATE TEMPORARY TABLE tmp (customer_id int, total int)

INSERT INTO tmp
SELECT customer_id, SUM(order_total) FROM yourtable group by customer_id


SELECT  
0
mherchlCommented:
which version are you using?

CREATE TEMPORARY TABLE tmp (customer_id int, total int)

INSERT INTO tmp
SELECT customer_id, SUM(order_total) FROM yourtable group by customer_id


SELECT  a.order_id, b.customer_id, a.order_total FROM yourtable a JOIN tmp b on a.customer_id = b.customer_id order by b.total desc, b.customer_id, a.order_total desc

DROP TEMPORARY TABLE tmp
0
AdrianSRUCommented:
I think this can be done in one query without the temporary table:

SELECT yourtable1.order_id, yourtable1.customer_id, yourtable1.order_total, IFNULL(SUM(yourtable2.order_total), 0) AS customer_total
FROM yourtable yourtable1 LEFT JOIN yourtable yourtable2 ON yourtable1.customer_id=yourtable2.customer_id
GROUP BY yourtable1.order_id ORDER BY customer_total DESC, yourtable1.order_total DESC;

The only difference between this and your desired output is that the customer total is included in the result set in my query.  That is necessary in order to sort by the SUM value.


--Adrian
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrianPap22Author Commented:
Works great, but has me curious:  how come your customer_total SUM function doesn't just return the sum for the entire orders table?
0
AdrianSRUCommented:
The GROUP BY clause groups the aggregate functions (SUM, COUNT, ...) by the field specified.  "GROUP BY yourtable1.order_id" makes it so that the SUM function works separately on each order_id value.


--Adrian
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.