[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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
0
BrianPap22
Asked:
BrianPap22
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now