Counting Items In A Hierarchical Category And Subcategories

I have two tables, one is a list of categories, the other a list of items listed in the categories. The category table is self-referencing through a ParentID column. Top-level categories have ParentID = 0.

ID int
ParentID int
CatName varchar(30)


There is a third table that links to items through ItemID, but this is not important for this problem!

What I want to do is create a stored SQL procedure. This procedure simply pulls rows from the category table and counts the number of items that are in it. This is straighforward enough using COUNT as a "virtual column", but the difficulty is counting the items in the category but also the items in any subcategories.

An end result with two top-level categories and three subcategories in each might look like:

- Category 1 (20)
---- Subcategory 1.1 (10)
---- Subcategory 1.2 (5)
---- Subcategory 1.3 (5)
- Category 2 (14)
---- Subcategory 2.1 (3)
---- Subcategory 2.2 (4)
---- Subcategory 2.3 (7)

Hence the difficult bit is getting the total number in brackets for each category with subcategories.

What I have at the moment is:

SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE Items.CategoryID = Categories.ID) AS ItemTotal
FROM Categories

What I would like is something along the lines of (pseudocode):

SELECT Categories.ID, Categories.CategoryName, Categories.ParentID, (SELECT COUNT(*) FROM Items WHERE <Item is in category or subcategory>) AS ItemTotal
FROM Categories

I don't particularly want to use temporary tables and I definitely do not want to do any of this retrieval in my application - it needs to come straight from the database.


View Replies


Counting Only Countries That Can Sell A Product Category?

In my data, I'm looking to find how many countries purchased a specific category, and how many countries total can sell the category in question.
(only certain countries sell each category)

This will probably be represented with a many to many relationship between country and category (in order to show the restriction between category and country).

How would one write a calculation to determine how many countries purchased a specific category, and how many countries total can sell the category in question?

View Replies View Related

Counting Items


I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?


View Replies View Related

Counting Items In Categories

Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with


Accommodation (10)
Real Estate(30)

Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by

Which Produces

Real Estate12401 12
Extreme Sports3 4

I would Like to get the same query to produce a list of all the empty records too.
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports3 4
Cycling 5 0

View Replies View Related

Counting Items Per Hour

I am trying to get a count of how many times a badge is entered in each hour it appears. In other words, I would like to find out how many times badge 3333 comes up in each hour of the day, the same with badge 4532.

The task at hand is to find out productivity for items processed per hour per badge number. I know I will need to take the hour out of timedate using datepart, but the results do not look right. Would anyone have any ideas on this? Thanks!

the table I have looks like this:

6112133333781267941/10/05 9:19
6112084532781275591/10/05 9:21
6111973333781265611/10/05 9:29
6111903333781261671/10/05 9:30
6111774532781253021/10/05 9:38
6111693333781257811/10/05 9:40

View Replies View Related

Counting Items And Returning Values

I have a table of product orders. It contains a row for "platform" and Ineed to return how many times each platform is listed in the DBExample data for platform could be:XBOXXBOXXBOXPLAYSTATIONPLAYSTATIONGAMECUBEPLAYSTATIONI'd like the data to be returned asXBOX - 3PLAYSTATION - 3GAMECUBE - 1How would I go about doing this please?

View Replies View Related

How Do I Do Categories And Subcategories And More Subcategories?

Hi, I have a database that will have various categories:

take this:

Kids:Boys Collection:Children Boys Collection:Children Boys Athletic:Athletic - Canvas

(it's shoes)

I have to put that in a structure that makes sense. I don't think having several tables for categories, subcategories, subsubcategories.... is the right way to do it. I'm not a design expert, so I was hoping someone might help me. How can I organize this? Any book, link, or advice is appreciated. Thank you.

View Replies View Related

Hierarchical Table Functions Vs Hierarchical CTE

Recently I was in need of a hierarchical tree data. I learned about CTE and how they can be used to build hierarchical data with simple syntax. I used CTE and was through with the task. Later during free time, I tried to compare CTE approach with the traditional SQL 2K Table Function approach. It was surprising to see the query costs when I ran both the modes at one go...

Query Cost (relative to batch) : 0.49%
Query Text : Select * From fn_GetTree(8);

Query Cost (relative to batch) : 99.51%
Query Text : with treedata (id, parentid, status, prevStatus, lvl) as (select ...)

What does that indicate? Does it mean that the Table Function approach is much faster than CTE? I am sure that I was not making unwanted Joins in the CTE mode.

Can someone explain why that huge difference is there? And what the scenarios where CTE is better over Table Functions?

View Replies View Related

Category/Parent Category Design And Querying


I have a simple table:


I want to associate my Products to a category so that I can search by category....ok.  If I just have a simlpe table:


I can link a product to a category.  Now, if I just link a product to a single category, such as the bottom leaf category:

Self Help / Personal Development / Spiritual / Meditation

I would link a product to the Meditation category.  However if I
click on Self Help while browsing, I want to see all items underneath
Personal Development, Spiritual and Meditiation.  So my question
is is this a good way to store the product-category relationships, or
should I put many entries into CategoryProducts to keep the queries
simlpe and faster? Are they faster doing it this way? In this way there
would be 4 entries for a product in meditation.  My personal idea
is that adding all entries up a tree arm of a category path will be
cumbersome to manage, but it does solve the problem of clicking on Self
Help and seeing all products that exist within sub-categories.  I
am sure an SQL query would be able to work this out, but I dont know if
performance would be something to consider on an ecommerce site? Are
there any patterns fo rthis stuff - seems a reasonably repeatable
pattern for business sites?



View Replies View Related

How To Create Categories Tree With Subcategories ?

Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:
--With country
--With world
How i write in Select Command ?
SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

View Replies View Related

How To Create Categories Tree With Subcategories ?

Hi, I Have 2 tables. First name of this tables is news_categories with one record: News. Strucutre this table ID and name_categories Second name of this tables is news_subkategorie with two records : With country and with world. Strucutre ID_Subcategories and name_subcategories I will receve effects:
--With country
--With world
How i write in Select Command ?
SELECT [ID_subcategries], [Name_subcategories], [ID] FROM [db_subcategories], [db_categories] WHERE (??)

View Replies View Related

DELETE Items Where Count(items) &>1

I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)

7fdfd 0

I want to DELETE each Row IN

SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1

and WHERE isOK = 0

in my exemple , after deleting, my table must look like


thank you for helping

View Replies View Related

Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category

I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those.  Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID   Category1      Saints2      Falcons3      Bucaneers4      Chargers5      FalconsPlayer Table:ID    CategoryID   Player                 News                                Player Last Updated1            1           Reggie Bush       Poetry in motion                                9/21/20062            1           Drew Brees         What shoulder injury?                        9/18/20063            5           Michael Vick       Break a leg, seriously.                       9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this.  I just don't know how to loop through and display it on a page.  Right now I have two datareaders in the code behind but ideally something like this, I would think the code  would go on the page itself, around the html.

View Replies View Related

Summing Invoice Items - The Multi-part Identifier &"items.TAX&" Could Not Be Bound

Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record.  My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam  ALTER PROCEDURE [dbo].[UpdateInvoiceSummary]    @Invoice_ID intAS    DECLARE @Amount intBEGIN    SELECT     Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX    FROM         InvoiceItems AS items    GROUP BY Invoice_ID    HAVING      (Invoice_ID = @Invoice_ID)    Update Invoices SET Amount = items.Amount    WHERE Invoice_ID =@Invoice_IDEND

View Replies View Related

Hierarchical Design

I am designing database that will store clinic and doctor information.

1) A clinic can have doctors and staff members.
2) A clinic can belong to another clinic.
3) A doctor can practice on his/her own practice/clinic and still belong to another clinic.

I will email my current design if needed.


Stephen Cantoria

View Replies View Related

Hierarchical XML Import?


Can anyone point me at a tutorial or sample that shows how to use IS for importing an xml file containing hierarchically arranged records ?

I have a file which contains multiple orders , the orders contain multiple line items.. the file also contains an element with details of the file source etc...

So, I want to make an insert in the FileLog table an then make inserts into the orders table .. then make inserts into the OrderItems table which will have the foreign key from the orders table in the records...

if you get what I mean...

But I have searched hign and low and can't see any info on how to load anything but a very flat xml file structure...



View Replies View Related

Hierarchical Cumulative Sum

I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.

The table is setup as follows:

Parent Child Pop
Europe France 0
France Paris 1
New York New York City 10
North America United States 0
North America Canada 0
United States New York 0
United States Washington 0
Washington Redmond 200
Washington Seattle 100
World Europe 0
World North America 0

This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.

I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):

World Null 311
World Europe 1
Europe France 1
France Paris 1
World North America 310
North America United States 310
North America Canada 0
United States New York 10
United States Washington 300
New York New York City 10
Washington Redmond 200
Washington Seattle 100

Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.

Any help/ideas would be appreciated! Thank you.

View Replies View Related

Hierarchical Recursive SUM Pb


I use Hierarchical Recursive to display website tree in my report.

The website has an unknown depth this is why I use this method.

My report Layout looks like this in BIDS : click here

The following piece of code displays hits by web page :

Code Block

The next one should displays hits by parent's folder :

Code Block

But it doesn't ...

It gives me the same value as for the web page hits.

What I am trying to achieve is to consolidate data by upper level. Something like this:


Any help will be very appreciated



View Replies View Related

Fabricated Hierarchical Recordset

I want to use fabricated hierarchical recordset in VB6 using ADO. I wrotecode likedim rs as adodb.recordestset rs=new adodb.recordestrs.fields.append "a1",adChar,30Then in loop I putrs.addnewrs("a1") I associated this with hierarchical flexgrid I saw what I expected. Onthe next step I added liners.fields.append "a2",adChapterand this operator gave me error that I use wrong parms. Then I realized thatI should use specific connection. But with this connection adChar stopped towork also. Is it possible to resolve this problem?--Aleks Kleyn

View Replies View Related

Hierarchical Table (count)

Hellofor MS SQL 2000 i am having :CREATE TABLE [dbo].[Items]([id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,[id_ItemsSup] [int] NULL,[Name] [nvarchar] (100) NOT NULL,[SubItems][int] DEFAULT (0)) ON [PRIMARY]with : UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1I get how many subItems has Item = 1how can I update the Column SubItems (for each row) ?to get the total of subItems for each Item ?thank you

View Replies View Related

Performing A Hierarchical Query...

Hi. I'm trying to find out which "cases" have a new items added to our database. I have provided a sample layout.

ID ParentID Name CreateDate
358 2 SMITH, JOHN 3/3/2008 11:15:23 am
359 358 Invoice 3/5/2008 4:13:52 pm
360 358 Shipping 3/5/2008 5:11:09 pm
361 358 Receiving 3/6/2008 4:22:01 am

The main ID for this is 358. The invoice, shipping, and receiving items are child items. I would like to run a query that can report which cases have newly added items. This is hierarchical I guess and I'm quite lost. I hope this makes sense. Thanks for any help!

View Replies View Related

Creating Hierarchical Tables

I need some help creating Hierarchical Tables. The scenario will be like this, I will store different keys as parents with children. e.g. "Status" should return "Active","Inactive","Obsolete", "Title" should return "Mr", "Mrs", "Ms","Prof","Dr" etc. The list is currently growing and need a solution. All help will be appreciated.

View Replies View Related

Flatten Hierarchical Data


I have a problem that I am hoping somebody can help me with!

I have built a hierarchy using the adjacency list model so I have records with an id that maps to the parent record so my hierarchy looks something like this:-


National Newspapers

Daily Express

Express Publications
Express Supplements
Daily Mail

Mail Publications
Mail Supplements

So my table would look like below:-

1 Newspapers Null
2 National Newspapers 1
3 Daily Express 2
4 Express Publications 3
5 Express Supplements 3

and so on. What I would like to be able to do is flatten out the hierarchy so I get something like below where each level is in a column.

NewsPapers National Newspapers Daily Express Express Publications
NewsPapers National Newspapers Daily Express Express Supplements

Ive used CTE's for displaying the hierarchy and producing aggregate figures when joing the hierarchy to spend information but am struggling to come up with any code that would produce a flattened hierarchy.

Any help would be greatly received!



View Replies View Related

Import Hierarchical Data

hi folks,

I have to import hierarchical text files like:

additionally I have to eleminate doubles. what is the best way for this problem ?
I have set up a flatfilesource with two columns and a conditional split on the first column
so now I have an output with [country;city;postalcode;street] and one with [name;firstname;salutation;title;age;nickname]. How do I split this in columns, put it in a dataset keeping the relations and remove doubles ?

Iam looking forward for any helping idea.


View Replies View Related

Filtering Hierarchical Data


Can any body tell me how can i filter hierarichal data in a table while doing Merge replication?

I have a table having a column self referencing to the primary key of one of the table record. This way

a parent-child-grandchikd relation is maintained between entities.

I want to filter this relationship on the basis of SUSER_Sname(). I have to parttion the data for the current user

so that all of its children and grand children can also be replicated.

Did anyone have had similar problem in past.

I would really appreciate the help.


View Replies View Related

Hierarchical Resultset Sorting

I have a query like this

with TempCTE(id, Name, level, sortcol)
Select id, Name, 0 as level,
cast(cast( id AS BINARY(4)) as varbinary(100)) sortcol
from Table1
where id = 1

union all

Select id, Name, 0 as level,
cast(sortcol + cast( id AS BINARY(4)) as varbinary(100)) sortcol

from Table1 inner join TempCTE on = Table1.parentid

select * from TempCTE order by sortcol

My problem is I want to sort this hierarchical resultset further on name like



View Replies View Related

Hierarchical Data In Result Set

How can I create a function that returns hierarchical data from a table with this structure:

- CategoryID
- CategoryName
- CategoryFather

I want to bring the result set like this...

CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2

How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.


View Replies View Related

Hierarchical Data Model


I would like to know best way to design the database for the following requirement. I have a collection of tree nodes. each node has a type and set of attributes and a parent node (except for the node which has no parent). node type refers to the level of the node in the tree. child node inherits the attributes from the parent node (similar to object oriented programming where derived class inherits properties of the base class). user can add/update/delete nodes from the tree and user can choose to override the attributes of the parent node in child node.
what is best way to store this type of data? should there be a separate table for each node type (level in the tree). but the problem with this approach is that we need to duplicate the columns of the parent node, because user can overwrite the parent node attributes in the child node. there can be more than one at the same level and all of them share same set of attributes. this concept is exactly like inheritance in object oriented programming. as far as the data is concerned, there are around 15 levels, around 30K nodes and 30 attributes spread across different node levels.


View Replies View Related

Storing Hierarchical In A Relational Database

What is the best approach for storing hierarchical
data in a database? For example, if I need to store a tree menu system,
how would I do that allowing for the most normalization within the
database, using the least number of queries/resources when pulling the
data out, and using the least amount of overhead both in storage and


View Replies View Related

Hierarchical Selection Within A Select Statment

CREATE TABLE RS_A(ColA char(1), ColB varchar(10))INSERT INTO RS_AVALUES ('S', 'shakespeare')INSERT INTO RS_AVALUES ('B', 'shakespeare')INSERT INTO RS_AVALUES ('P', 'shakespeare')INSERT INTO RS_AVALUES ('S', 'milton')INSERT INTO RS_AVALUES ('P', 'milton')INSERT INTO RS_AVALUES ('B', 'shelley')INSERT INTO RS_AVALUES ('B', 'kafka')INSERT INTO RS_AVALUES ('S', 'kafka')INSERT INTO RS_AVALUES ('P', 'tennyson')SELECT * FROM RS_ANow i need a select which selects based on hierarchyif ColA = 'S', then select only that rowelse if ColA = 'B' then select only that rowelse if colA = 'P' then select only that rowSo my results should look likeS shakespeareS miltonB shelleyS kafkaP tennysonIs there a way to do this within a select statementI tried using a CASE in WHERE CLAUSE but it put out all rows whichexisted/If any of you can help me with this right away, its is greatlyappreciatedThanks in advance

View Replies View Related

Help With Bottom To Top Query Into Hierarchical Data

Hello to all:First let me apologize for the length of this question, I've made an attemptto include as much information as is needed to help with the question.I am having problems putting together a query to pull out an alternativehierarchical view of my data. The database is implemented under SQL Sever2000 and I am writing the front end using VB.Net and The followingis the portion of my database structure that I am having problems with. Inthis diagrameach table has a one to many relationship with the table beneath it (Theprimary key for each table is the table name with ID appended, and theforeign keys for each child table refernce the primary key in the tableabove).Deals|Contracts___|Offers || | || |__OfferDetails| |Acceptances || ||__AcceptanceDetailsNotice that an Offer and it's associated Acceptances can be in more than oneContract, the OfferDetails describe how the Offer is broken up into eachContract.While there is a one to many relationship between Contracts andOfferDetails, there would only ever be a single OfferDetail per Contract perOffer (i.e. splitting up the Offer means assigning one and only oneOfferDetail to each Contract it is assigned to). This info may not berelavant, but it can be counted on as true. This doesn't hold true with theAcceptanceDetails however, there can be multiple AcceptanceDetails perAcceptance associated with OfferDetails.I wish to establish a couple of different heierachical grids into this dataas part of my user interface:View by Deals (In this view we see only the part of Each Offer associatedwith the Deal at the top of thehierarchy):Deal1Contract1Offer1 + OfferDetails associated with Contract1Acceptance1 + AcceptanceDetails assoctaited with OfferDetails above.Deal2Contract2Offer1 + OfferDeatils assoctaited with Contract2Acceptance1 + AcceptanceDetails associated with OfferDetails above.This grid has been relativly straitforward to figure out, as the hierrchicalinformation is alreadystructured to plug it into a hierrchical grid control easily (aftercombining the Offers and OfferDetailsinto a new table).View by Offer/Acceptance (In this view we see the entire Offer+Acceptance asthe top of the hierarchy,and each Deal (and associated Contract) underneath the Offer+AcceptanceOffer1+Acceptance1 (with all the details rolled up into a summuray includedin this row).Deal1Contract1Deal2Contract2This is the grid which is causing me problems, the problem area beingputting together a query which retrieves each DealID assoctiated with theOfferID at the top of the hierarchy. I need a query which essentiallyStarts with the OfferID, pulls each OfferDetailID associated with it, thenmoves upstream tofind each ContractID assoctaed with the OfferDetailsID (saving those ID'sfor use in third level of the grid, since there can be more Contracts underthe Deal which are not associated with the Offer we're looking at), and thenmoves upstream again to finally get the DealID which forms the second levelof the hierarchy.Can somebody help me with what such a query (or queries, as I assume I'llneed one to get the DealID's and another to get the ContractID's, althoughthe former might use the latter as a sub-query) might look like? I'm not atall good with T-SQL, but I'm learning fast! I haven't been able to find anyexamples onhow to go backwards up a tree to get to a record. I'll most likely beimplementing these as stored procedures, as these grid views are integral tomy userinterface.Thanks in advance to any who take the time to reply!

View Replies View Related

Copyrights 2005-15, All rights reserved