Question
SQL query... query. Ok heres my task: Return the salesman first name, last name and total of the product price for the listings made by
SQL query... query.
Ok heres my task:
Return the salesman first name, last name and total of the product price for the listings made by each salesman. You need to return only those records, whose total of the product price is more than 10000. Make sure you display your result set in the order of the total of the product price.
I have three tables in this database, salesman, products and departments. 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 helped me 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_ID and SUM(b.Products_Price) > 10000
but it didnt work ( i may have not given them enought information) and i kept getting the "every derived table must have its 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 any effect on the query. I was thinking i need a GROUP BY clause to accomapany the SUM function in order to fix the single row output? but not sure why the sum isnt working in adding up the price totals for each salesman..
Please help, ive spent the last couple of days watching tutorial after tutorial but i cant seem to get this.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started