SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 join 2 tables order by sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

joethermal
Starting Member

1 Posts

Posted - 08/25/2009 :  06:33:12  Show Profile  Reply with Quote
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...

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 incorporate sum and order by...
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

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 08/25/2009 :  06:42:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
SELECT sales.customer, details.name , SUM(amount) AS total
FROM sales LEFT JOIN details
ON sales.customer = details.customer
WHERE sales.market ='furniture'
GROUP BY sales.customer, details.name
ORDER BY total DESC

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000