Problem solve Get help with specific problems with your technologies, process and projects.

Top N subcategories

I want a query to summarize categories of information so that the top N subcategories within each major category are displayed. All category codes and associated elements are in the same table.

Thanks for making part of this problem easy. Ordinarily, one would not expect to find "associated elements" in the category table. For example, product sales would be in a sales table, which would be joined to a product master table, which would in turn be joined to the category table, and then there would be a GROUP BY on the category to get SUM(productsales). However, if these joins are "pre-defined" into a view, then we can simply use the view in the same way that we're going to use the sample category table defined below, which has the sales data incorporated as one of the "associated elements". It makes the query that much easier.

If you're comfortable with a self-referencing hierarchical table, you can skip right to Part 3 of this answer. The remainder of Parts 1 and 2 will be devoted to building up the category hierarchy, for those who are new to the structure.

We'll start by populating the category table with the "top level" or major categories:

 table categories
     ( id        smallint identity not null 
     , parentid  smallint              null
     , descr     varchar(15)       not null
     , salesamt  integer               null  
     , constraint categories_pk
           primary key ( id )
     , constraint parent_category_fk
           foreign key ( parentid )
           references categories ( id )
  into categories ( descr )
           values ('Category A') 
  into categories ( descr )
           values ('Category B') 
  into categories ( descr )
           values ('Category C') 
  into categories ( descr )
           values ('Category D')

Now let's run a SELECT to see what we've got so far:

select * from categories           

id parentid descr salesamt ------ -------- --------------- ----------- 1 NULL Category A NULL 2 NULL Category B NULL 3 NULL Category C NULL 4 NULL Category D NULL

Notice that the parentid is null; this is what identifies categories at the top level. (They don't have sales amounts, either; they could, but that would make the query messier.)

Now let's add a subcategory:

  into categories ( descr, parentid, salesamt )
           values ( 'subcat A1', 1, 103 )

Notice that the parentid value for this subcategory is given as 1, which corresponds to Category A. In other words, we need to know the value of the parent category's id before we can insert a subcategory under that category.

Now let's assume we've added several additional subcategories in this manner, and run another SELECT to see the categories and their subcategories:

id     parentid descr           salesamt    
------ -------- --------------- ----------- 
1      NULL     Category A      NULL
2      NULL     Category B      NULL
3      NULL     Category C      NULL
4      NULL     Category D      NULL
5      1        subcat A1       103
6      1        subcat A2       207
7      1        subcat A3       94
8      1        subcat A4       255
9      1        subcat A5       159
10     4        subcat D1       150
11     4        subcat D2       195
12     2        subcat B5       315
13     2        subcat B4       301
14     2        subcat B3       377
15     2        subcat B2       315
16     2        subcat B1       315

Continued in Part 2.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.