Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: TOP 10 Results

  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Question Unanswered: TOP 10 Results

    I have to do a top 10 query. Here is the query:

    SELECT DISTINCTROW TOP 10 wo.factor*wo.listprice AS TotalSold,
    b.companyName AS company_Name
    FROM tblWorkOrder AS wo, tblBss AS b
    WHERE wo.bussID=b.bussID
    AND wo.requestedShipWeek>=#1/1/2004#
    AND wo.requestedShipWeek<=#1/31/2004#
    ORDER BY wo.factor*wo.listprice DESC;

    Now, if i run this query and there is only one company that has a requestedShipDate within that range, then it displays the same company 8 times (there are 8 jobs in that month). Why wouldn't it just list it once? And, on other months, it lists more than 10 as well.
    Any ideas?

  2. #2
    Join Date
    Sep 2003
    Location
    Gloucester
    Posts
    28
    I have created this query on one of my tables - it does give the top ten plus any other lines of the same value within the top ten.

    Play with it and see if you can get to where you are going

    SELECT TOP 10 [forward and back].SURNAME, Count([forward and back].GF) AS CountOfGF
    FROM [forward and back]
    WHERE ((([forward and back].DATESIGNED) Between #1/1/1992# And #12/31/1992#))
    GROUP BY [forward and back].SURNAME
    ORDER BY Count([forward and back].GF) DESC;

  3. #3
    Join Date
    Oct 2003
    Posts
    73
    thx for the response!
    I'll give it a try and let you know how it goes.



    Originally posted by Jumper
    I have created this query on one of my tables - it does give the top ten plus any other lines of the same value within the top ten.

    Play with it and see if you can get to where you are going

    SELECT TOP 10 [forward and back].SURNAME, Count([forward and back].GF) AS CountOfGF
    FROM [forward and back]
    WHERE ((([forward and back].DATESIGNED) Between #1/1/1992# And #12/31/1992#))
    GROUP BY [forward and back].SURNAME
    ORDER BY Count([forward and back].GF) DESC;

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Question

    Hello,

    Insted of DISTINCTROW try DISTNICT only

    Perhps

  5. #5
    Join Date
    Oct 2003
    Posts
    73
    well, im gonna ask a noob question here:

    I couldn't get that to work. I can't get it to display some of the names only once.
    I want it to get the top 10 buyers from a table. What happens if there aren't 10? I tried to determine what happens if i know theres only 6 companies in between those dates and it still gives me some duplicates.
    so, can anyone point me to a good site that explains this? I really need to nail this down.

    thx for ur help thusfar.

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    SELECT TOP 10 Sum(wo.factor*wo.listprice) AS TotalSold,
    b.companyName AS company_Name
    FROM tblWorkOrder AS wo, tblBss AS b
    WHERE wo.bussID=b.bussID
    AND wo.requestedShipWeek>=#1/1/2004#
    AND wo.requestedShipWeek<=#1/31/2004#
    ORDER BY wo.factor*wo.listprice DESC
    Group by b.companyName;

    I did not test this, so the Group by may have to be ahead of the order by...

  7. #7
    Join Date
    Oct 2003
    Posts
    73
    jmrSudbury,
    thx for the reply

    I still come up with the same results (double businesses). I think i determined the reason for that though....its friday afternoon.

    also, the 'group by' has to go in front of the 'order by'. thx for the help though. if you got anymore suggestions, feel free to fire away

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    I can't get it to display some of the names only once
    that's because you have wo columns in the SELECT and aren't grouping

    you have to decide what you want the top 10 of -- companies or work orders

    if you want companies, then you do a GROUP BY on Company_name, and then there will be only one amount per company

    make sense?


    rudy
    http://r937.com/

  9. #9
    Join Date
    Oct 2003
    Posts
    73
    hey rudy,
    thx for replying.

    What i need is to find the top 10 companies based on sales. That is why I multiply the wo.factor*wo.listprice and sum it for in between the dates specified.

    SELECT TOP 10 Sum(wo.factor*wo.listprice) AS TotalSold,
    b.companyName AS company_Name
    FROM tblWorkOrder AS wo,
    tblBusiness AS b
    WHERE wo.businessID=b.businessID
    AND wo.requestedShipWeek>=#1/1/2004#
    AND wo.requestedShipWeek<=#1/31/2004#
    Group by b.companyName, wo.factor*wo.listprice
    ORDER BY wo.factor*wo.listprice DESC

    If I DON'T add in the wo.factor*wo.listprice after the Group by, it gives me the following message: "you tried to execute a query that does not include the specified expression 'wo.factor*wo.listprice' as part of the aggregated function."

    EDIT: (accidentally pressed enter to post. )

    So what am i doing wrong? am i able to to get top 10 sum(wo.factor*wo.listprice) to get the total amount fo a company?
    Last edited by tkat11; 10-20-03 at 12:04.

  10. #10
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    It is probably grouping by the date as well. One solution would be so create a query to show all the records that are within the correct date range. Next use that query as the source for a query that does the calculation and sum and group by.

  11. #11
    Join Date
    Oct 2003
    Posts
    706

    Re: TOP 10 Results

    [i]
    SELECT DISTINCTROW TOP 10 wo.factor*wo.listprice AS TotalSold,
    b.companyName AS company_Name
    FROM tblWorkOrder ...;

    Now, if i run this query and there is only one company that has a requestedShipDate within that range, then it displays the same company 8 times (there are 8 jobs in that month).
    Try "SELECT DISTINCT." Not DISTINCTROW.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  12. #12
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Distinct is not required with a group by.

    SELECT TOP 10 Sum(wo.factor*wo.listprice) AS TotalSold, b.CompanyName AS company_Name
    FROM tblWorkOrder AS wo INNER JOIN tblBss AS b ON wo.BussId = b.bussID
    WHERE (((wo.requestedShipWeek)>=#8/1/03# And (wo.requestedShipWeek)<=#1/31/04#))
    GROUP BY b.bussName;

    it worked with this data:

    woNum ListPrice factor requestedShipWeek BussId
    1 $23.00 1 10/10/03 1
    2 $25.00 1 11/10/03 1
    3 $24.00 2 6/10/03 2
    4 $42.00 1 9/12/03 2

    and gave this:

    TotalSold company_Name
    $48.00 abc
    $42.00 def

    too bad my tabs were not kept though!

  13. #13
    Join Date
    Oct 2003
    Posts
    73
    ur right, that query does run proplery (didn't verify the data but it seems alright). The only thing is, if i want to add 'ORDER BY wo.factor*wo.listprice DESC' at the end, it gives me the aggregated function message "you tried to execute a query that does not include the specified expression 'wo.factor*wo.listprice' as part of the aggregated function."

  14. #14
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I get that one a lot. Change to the Query by Example screen and change the Total: row to Expression for that column.

  15. #15
    Join Date
    Oct 2003
    Posts
    73
    thx for replying.

    I'm not sure what you mean by going to the query by example screen. Could you clairfy?

    Watch me figure this out right after i post...

Posting Permissions

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