We help IT Professionals succeed at work.

GROUP BY, but backwards

BrianPap22
BrianPap22 asked
on
Medium Priority
320 Views
Last Modified: 2008-03-06
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
Comment
Watch Question

Commented:
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  

Commented:
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
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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?
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.