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 dothanks - joe(using php4 and t-sql , SQL server 2003)sales tableid customer market amount-------------------------------------1 1003 furniture 223.552 1002 furniture 120.003 1004 marine 345.404 1003 furniture 100.005 1001 furniture 50.006 1003 furniture 500.007 1005 furniture 625.00details tableid customer name phone-------------------------------------1 1001 tim 021 445 67862 1002 terry 021 445 34443 1003 john 027 456 33444 1004 andrew 021 334 57445 1005 barry 022 445 8721output wanted, ordered by sum of total amount...customer name total------------------------------1003 john 823.551005 barry 625.001004 andrew 345.401002 terry 120.001001 tim 50.00i 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 totalFROM sales LEFT JOIN details ON sales.customer = details.customer WHERE sales.market ='furniture' ORDER BY total DESC
join 2 tables order by sum
Posted - 2009-08-25 : 06:33:12
