SQL query… query.
Ok heres my task:
Return the salesman first name, last name and total of theproduct price for the listings made by each salesman. You need toreturn only those records, whose total of the product price is morethan 10000. Make sure you display your result set in the order ofthe total of the product price.
I have three tables in this database, salesman, products anddepartments. salesman structure is: (salesman_ID (INDEX),firstname, lastname, phone, dept_ID)
products structure is: ( product_ID (INDEX), type, size, note,price, salesman_ID).
Departments structure is: (dept_ID (INDEX), dept_name, address,phone_number)
I was having trouble getting the join to work and someone helpedme by suggesting this query:
SELECT A.salesman_Firstname, A.salesman_Lastname,B.Products_Price FROM (SELECT salesman_ID, salesman_Firstname,salesman_Lastname FROM salesman) as A, (SELECT salesman_ID,Products_Price FROM Products) WHERE A.salesman_ID = B.salesman_IDand SUM(b.Products_Price) > 10000
but it didnt work ( i may have not given them enoughtinformation) and i kept getting the “every derived table must haveits own alias” error.
So i modified the query as follows:
SELECT A.Firstname, A.Lastname, B.Price FROM
(SELECT salesman_ID, Firstname, Lastname FROM salesman) as A,
(SELECT product_ID, Price FROM products) AS B
WHERE A.salesman_ID = B.product_ID HAVING SUM(B.Asking) >10000
Now i get the following result from this query:
Firstname Lastname Price
Jeff Jansen 7560
So now the problem is that i am only getting one row returned (should be alot more) and the SUM function doest seem to have anyeffect on the query. I was thinking i need a GROUP BY clause toaccomapany the SUM function in order to fix the single row output?but not sure why the sum isnt working in adding up the price totalsfor each salesman..
Please help, ive spent the last couple of days watching tutorialafter tutorial but i cant seem to get this.