Query / Ordering help PLZ

[rjh]

Hello all -
I have a query/ordering scenario I need assistance with.
I have ONE database which contains data that would look similar to the following:

CustomerA, productA, $1.00
CustomerA, productB, $2.00
CustomerA, productC, $3.00
CustomerA, productD, $4.00
CustomerA, productS, $4.00

CustomerB, productB $2.00
CustomerB, productC, $3.00
CustomerB productD, $4.00

CustomerC, productA, $6.00
CustomerC, productC, $22.00
CustomerC, productD, $3.00

CustomerD, productA, $7.00
CustomerD, productB, $4.00
CustomerD, productC, $5.00

CustomerE, productB, $2.00
CustomerE, productC, $3.00
CustomerE, productD, $4.00

CustomerF, productA, $1.00
CustomerF, productX, $2.00
CustomerF, productK, $3.00
CustomerF, productG, $14.00

I need help in defining the most efficient way of doing this:

Of the customers who have productA, (which in my example data would be customers A, C, D and F), I need to order them in descending order by the customer who has spent the most money OVERALL (all products included in total)

So, in my example above, for the customers who have productA - their totals would be as follows:

CustomerA: $14.00
CustomerC: $31.00
CustomerD: $16.00
CustomerF: $20.00

Therefore my resulting order would need to be: (DESC order)

CustomerC
CusotmerF
CustomerD
CusotmerA

I do not have a customers TOTAL field anywhere - can't do it. So I need to find a way to list only customers that have a certain product, but order my results based on the TOTAL of ALL their products. Hope that makes sense.

Any help would be GREATLY appreciated!

Ron

[sneakyimp]

So the customers must have purchased product A and you want to order them by their TOTAL purchase of all products? Perhaps this (which may not work in all versions of mysql). I'm assuming you have one table called 'table' with the fields customer, product, and amount. i'm also assuming that the amount field is not a text field but rather a numeric field of some kind (like int or decimal)

SELECT customer FROM table WHERE customer IN (SELECT DISTINCT customer WHERE product='productA') GROUP BY customer ORDER BY SUM(amount) DESC

Or something like that. Note that none of my assumptions may be true.