hi, i have 2 tables sales and details as shown below, i would to have the output shown below, but i am having problems joining and sorting by the sum, can anyone give me a clue as to what i need to do
thanks - joe

(using php4 and t-sql , SQL server 2003)

sales table

id customer market amount
-------------------------------------
1 1003 furniture 223.55
2 1002 furniture 120.00
3 1004 marine 345.40
4 1003 furniture 100.00
5 1001 furniture 50.00
6 1003 furniture 500.00
7 1005 furniture 625.00



details table

id customer name phone
-------------------------------------
1 1001 tim 021 445 6786
2 1002 terry 021 445 3444
3 1003 john 027 456 3344
4 1004 andrew 021 334 5744
5 1005 barry 022 445 8721



output wanted, ordered by sum of total amount...

customer name total
------------------------------
1003 john 823.55
1005 barry 625.00
1004 andrew 345.40
1002 terry 120.00
1001 tim 50.00

i can output customer and name with this...
Code:
SELECT DISTINCT sales.customer, details.name 
FROM sales LEFT JOIN details 
ON sales.customer = details.customer 
WHERE sales.market ='furniture' 
ORDER BY sales.customer DESC

but have been struggling to get the sum and order by sum...

Code:
SELECT DISTINCT sales.customer, details.name , SUM(amount) AS total
FROM sales LEFT JOIN details 
ON sales.customer = details.customer 
WHERE sales.market ='furniture' 
ORDER BY total DESC