1

I would like to select the top 7 categories by spending and then have the rest grouped as 'Others.' It seems like this code works, however it takes at least 20 minutes to run, I think because NOT IN sometimes does not work with indexing.

My database has ~20k records

SELECT [Category], [Total Spending] FROM

   (SELECT TOP 7  [Category], SUM([Spending]) AS [Total Spending]
      FROM Data
      GROUP BY [Category]
      ORDER BY SUM([Spending]) DESC
   ) AS Q1

   UNION ALL

   SELECT'Other' AS [Category], SUM(Spending) AS [Total Spending]
      FROM Data
      WHERE Category NOT IN 
         (SELECT TOP 7 [Category]
          FROM Data
          GROUP BY [Category]
          ORDER BY SUM([Spending]) DESC)

My Question is a combination of these two questions, both answered:

Select Top (all but 10) from ... in Microsoft Access

Very slow subqueries when using "NOT IN"

Community
  • 1
  • 1
JRob
  • 55
  • 1
  • 10

1 Answers1

0

give first select into temp table. select in 2nd query. Use Category from first temp. Then use union of select and temp.

SpiderCode
  • 10,062
  • 2
  • 22
  • 42
vvv
  • 1
  • Thanks for your answer. Can you please be a little more specific on the syntax for creating a temp table in this situation within one query? I did an hour or so of research on it, but couldn't get it to work. – JRob Feb 07 '14 at 14:11