Cant think how to do....

Hi guys..if anyone has the time, could some1 help me with a querie i am trying to do on my DB..i attached the tables..and well,

i am trying to do...given the start and end dates, produce report showing the performance of each sales rep. over that period. should begin with the rep who has most orders by value and include total units sold and total order value

geez, just writing what i wanna do sounds complicated.
if anyone can gimme a hand it would be much appreciated!
thanksoh, this sounds so much like a school assignment

the only way i help people with homework is if they've already written the sql and shown their thinking process, so that i can offer corrections or suggestions for improvement|||Hi, yes it is a question sheet i have to do...and i understand why you said what you said...so ill try to explain what i understand from the question and hopefully you can help me :)

well from what i can see, i am going to have to join the salesRep table with ShopOrder. and use the SalesRepID as the link. but, its what comes next that is just too mixed up for me...i am guessing i have to do a sum of the salesrep...that then mean i have to link to the orderline table to see the quantity right??
hmmmm, ok...i am gonna have another think..i am talking rubbish!
ill edit this in a bit....tbc|||you're on the right track

join SalesRep to ShopOrder

can't stop there, must also join to OrderLine, to get the value of Quantity*UnitSellingPrice

now sum those up, so we need a GROUP BY

how about by SalesRep.Name

don't forget to sum the number of units sold, too|||ok, this is where i am at...thanks for the help :)

SELECT salesRep.salesRepId, salesRep.Name, SUM(Quantity*UnitSellingPrice) AS "units sold"
WHERE salesRep.salesRepID = ShopOrder.salesRepID, orderLine.ShopOrderID = ShopOrder.ShopOrderID
FROM salesRep, ShopOrder, OrderLine
GROUP BY salesRep.Name;

something is wrong there right??
thanks for the guidance :)|||WHERE comes after FROM

clauses in the WHERE must be separated by ANDs, not commas

alternative: use JOIN syntax, and replace your FROM and WHERE with this:
select ...
from salesRep
inner
join ShopOrder
on salesRep.salesRepID = ShopOrder.salesRepID
inner
join OrderLine
on ShopOrder.ShopOrderID = orderLine.ShopOrderID
group
by ...

the GROUP BY must have all the non-aggregate columns in the SELECT list|||i think i understand that way of joining.....this way is better?
i am getting a error msg:

mysql> select salesRep.salesRepId, salesRep.Name
-> from salesRep inner join ShopOrder on salesRep.salesRepID = ShopOrder.sal
esRepID
-> inner join OrderLine on ShopOrder.ShopOrderID = orderLine.ShopOrderID
-> group by salesRep.salesRepId, salesRep.Name;
ERROR 1109: Unknown table 'orderLine' in on clause

i didnt put the quantity yet because i want to check that it works...am i way off?|||case sensitive table name

sorry, that was my bad

:(|||thats ok, i cant believe i didnt spot it!!!! must cos my brain has turned into jello!!!....

select salesRep.salesRepId, salesRep.Name, SUM(Quantity*UnitSellingPrice) AS "Total"
from salesRep inner join ShopOrder on salesRep.salesRepID = ShopOrder.salesRepID
inner join OrderLine on ShopOrder.ShopOrderID = OrderLine.ShopOrderID
group by salesRep.salesRepId, salesRep.Name;

i gota that working...but i am having trouble understanding, or breaking down the question..ie what it wants, its confusing me!! :( this bit..

" the rep who has most orders by value and include total units sold and total order value "

thanks for all the help...ur a star|||"begin with the rep who has most orders by value"

i.e sort the results by Total descending

don't forget your other sum for number of units|||select salesRep.salesRepId, salesRep.Name, SUM(Quantity*UnitSellingPrice) AS "Total", SUM(Quantity) AS "Units", SUM(OrderLine.ShopOrderID) AS "Total Orders"
from salesRep inner join ShopOrder on salesRep.salesRepID = ShopOrder.salesRepID
inner join OrderLine on ShopOrder.ShopOrderID = OrderLine.ShopOrderID
group by salesRep.salesRepId, salesRep.Name
ORDER by "Total" DESC;

i think i got it ? right...seems to work...

now to do the bit about given start and end dates?
i tried putting a WHEN and giving dates but it didnt work :(
honestly i do try before bugging you, i feel useless having to ask so much.....but thanks, if you have time to show me, brilliant, thanks...|||not WHEN, WHERE

and if fred dobbs sold an order with ShopOrderID = 21, if there were 5 items in that order, his "Total Orders" would be 105, and would be wrong