Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    16

    Unanswered: 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!
    thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    16
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    16
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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:
    PHP Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Posts
    16
    i think i understand that way of joining......this way is better?
    i am getting a error msg:

    PHP Code:
    mysqlselect salesRep.salesRepIdsalesRep.Name
        
    -> from salesRep inner join ShopOrder on salesRep.salesRepID ShopOrder.sal
    esRepID
        
    -> inner join OrderLine on ShopOrder.ShopOrderID orderLine.ShopOrderID
        
    -> group by salesRep.salesRepIdsalesRep.Name;
    ERROR 1109Unknown table 'orderLine' in on clause 
    i didnt put the quantity yet because i want to check that it works...am i way off?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    case sensitive table name

    sorry, that was my bad

    Last edited by r937; 03-26-04 at 00:29.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2004
    Posts
    16
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2004
    Posts
    16
    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...

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •