Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Group 'Other' items not in the 'Top 5' (XP)

    (Edited by HansV to format data as table)

    This is probably simple for many of you, but not only can I not accomplish it on my own, I have not been able to find a reference to this in archives.
    For the sake of argument, assume I have a field called "ItemName" in a table, that contains a few hundred records and there 15 or so unique item names in the ItemName field. I want a report that shows the Top 5 Item Names sold with the count and percentage (easy part) PLUS a category "Other" that contains the count all remaining records that are not included in the Top 5. How is this accomplished?
    Example:

    <table border=1><td>ITEM</td><td>NUMBER</td><td>Slinky</td><td align=right>250</td><td>Hoola Hoop</td><td align=right>200</td><td>Buck Rogers</td><td align=right>188</td><td>Dale Evans</td><td align=right>145</td><td>Operation</td><td align=right>107</td><td>Other</td><td align=right>105</td></table>
    Where "Other" is the sum of the count of all other items.

    Thanks for your consideration.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Group 'Other' items not in the 'Top 5' (XP)

    This is, in fact, not trivial. You need three queries to do this:

    1. A query qryTop5 to determine the top 5. The SQL looks like this (with the appropriate names substituted, of course):

    SELECT TOP 5 ItemName, Count(*) AS Number, 1 AS Sort FROM TableName GROUP BY ItemName ORDER BY Count(*) DESC

    2. A query qryOther to count the items not in the top 5. The SQL looks like this:

    SELECT "Other" AS ItemName, Count(*) AS Number, 2 AS Sort FROM TableName WHERE ItemName Not In (SELECT ItemName FROM qryTop5)

    3. A Union query to combine the results of qryTop5 and qryOther:

    SELECT ItemName, Number FROM qryTop5
    UNION SELECT ItemName, Number FROM qryOther
    ORDER BY Sort, Number DESC

    In case you're wondering, the extra column Sort is used to ensure that the "Other" category will be displayed below the top 5 in the union query.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Group 'Other' items not in the 'Top 5' (XP)

    Hans,

    You are incredible! The extra "Sort" column is a great touch.

    Thanks,

    Ken

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Group 'Other' items not in the 'Top 5' (XP)

    Hans,

    The first two queries work like fine clockwork. However, the second results in the following error message:
    “The ORDER BY expression (Sort) includes fields that are no selected by the query. Only those fields requested in the first query can be included in an ORDER BY expression.”

    When I remove the Sort from the ORDER BY it does run, but uputs the "Other" into the mix, instead of last.
    The queries:
    1 No Problems with this executing.
    SELECT TOP 5 ItemName, Count(*) AS Num, 1 AS Sort
    FROM tblInventory
    GROUP BY ItemName
    ORDER BY Count(*) DESC;

    2. No problem with this one executing
    SELECT "Other" AS ItemName, Count(*) AS Num, 2 AS Sort
    FROM tblInventory
    WHERE (((ItemName) Not In (SELECT ItemName FROM sqTop5)));

    3. Problem only when I include the "ORDER BY Sort, "
    SELECT ItemName, Num FROM sqTop5
    UNION SELECT ItemName, Num FROM sqOtherCauses
    ORDER BY Sort, Num DESC;

    4. Same as above sans "ORDER BY Sort". Like this it runs, however, without the Sort included I don't have access to the "1" and "2" created in the first two select queries.
    SELECT ItemName, Num FROM sqTop5
    UNION SELECT ItemName, Num FROM sqOtherCauses
    ORDER BY Num DESC;

    What am I overlooking?

    Also, my first post stated "a few hundred" records and should have been "thousands". I executed and timed the second query six times each for 1000, 2000, 3000, and 4000 records. The second query average times are 4 seconds, 14.4 seconds, 33 seconds and 58.8 seconds when run against 1000, 2000, 3000 and 4000 records respectively. The machine specs are:
    Pentium 4, 2.8 mhz, 512 meg RAM, XP Home Edition and Access XP the only app running. From 2000 to 3000 and 3000 to 4000 the times are doubling for each addition of 1000 records. Is that to be expected?

    Thanks again in advance for your guidance.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Group 'Other' items not in the 'Top 5' (XP)

    Sorry for not testing thoroughly enough. Try this SQL for the union query:

    SELECT ItemName, Num, Sort FROM sqTop5
    UNION SELECT ItemName, Num, Sort FROM sqOtherCauses
    ORDER BY Sort, Num DESC;

    or even simpler

    SELECT * FROM sqTop5
    UNION SELECT * FROM sqOtherCauses
    ORDER BY Sort, Num DESC;

    By including Sort in the fields selected from both contributing queries, you can use it in the ORDER BY clause.

    And replace the SQL for sqOtherCauses by this, it is MUCH faster:

    SELECT "Other" AS ItemName, Count(*) AS Num, 2 AS Sort
    FROM tblInventory LEFT JOIN sqTop5 ON tblInventory.ItemName = sqTop5.ItemName
    WHERE sqTop5.ItemName Is Null;

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Group 'Other' items not in the 'Top 5' (XP)

    Hans,


    THANKS! That got it!

    And on top of the query producing what I hoped, with the change to the second query using the JOIN, it consistently runs through over 22,000 records in under 5 seconds. What a HUGE difference~!

    Thank you!

Posting Permissions

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