Counting Items In A Hierarchical Category And Subcategories

Mar 30, 2008

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 2 Replies


Counting Only Countries That Can Sell A Product Category?

May 20, 2008

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 9 Replies View Related

Counting Items

Jun 22, 2004


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 6 Replies View Related

Counting Items Per Hour

Nov 2, 2005

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 4 Replies View Related

Counting Items In Categories

Oct 28, 2007

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 2 Replies View Related

Counting Items And Returning Values

Jul 20, 2005

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 1 Replies View Related

How Do I Do Categories And Subcategories And More Subcategories?

May 1, 2006

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 7 Replies View Related

Category/Parent Category Design And Querying

Jun 29, 2005


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 5 Replies View Related

Hierarchical Table Functions Vs Hierarchical CTE

Jun 9, 2006

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 8 Replies View Related

How To Create Categories Tree With Subcategories ?

Dec 17, 2006

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 1 Replies View Related

How To Create Categories Tree With Subcategories ?

Dec 17, 2006

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 4 Replies View Related

Ascending Categories And Ascended Subcategories

Apr 18, 2014

I've been trying some examples on line and have not been able to get this to work.

Here is my table followed by what I am trying to output.

Id | ParentId | CategoryName

33 | 0 | Fruits
34 | 0 | Vegetables
35 | 0 | Meats
37 | 33 | Grapes
38 | 33 | Oranges

[Code] ....

I want to output this for a dropdownlist in C# with ascending categories and ascended subcategories

48 0 Electronics
33 0Fruits
3933 -Apples
4033 -Bananas
3733 -Grapes
3833 -Oranges

[Code] ...

This is what I have currently, still a long way off :/

SELECT TOP (100) PERCENT fcat.Id AS fcat_id, fcat.CategoryName AS fcat_name, fcat.ParentCategory AS fcat_parent, fsub.Id AS fsub_id,
fsub.CategoryName AS fsub_name, fsub.ParentCategory AS fsub_parent
FROM dbo.ProductCategories AS fcat LEFT OUTER JOIN
dbo.ProductCategories AS fsub ON fcat.Id = fsub.ParentCategory
ORDER BY fcat_name, fcat_id, fsub_name

View 2 Replies View Related

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

Apr 17, 2007

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 3 Replies View Related

SQL Server 2012 :: Identify Sets That Have Same Items (where Set ID And Items In Same Table)

Feb 25, 2015

I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:



You can see that there are some sets that have the same members:

- 1 and 10
- 2 and 11
- 7, 8 & 9

What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.

View 8 Replies View Related

Reporting Services :: Group And Sum Items / Sub-items Into One Record

Apr 10, 2015

I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).

I have a table of items (i.e. products).  In some cases, items can be components of another item (called "Kits").  In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items".  In our Items table, we have a field called "Parent_Item_Id".  Records for Child Items contain the Item Id of the parent.  So a sample of my database would be the following:

ItemId | Parent_Item_Id | Name | QuantityAvailable
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100

Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.

So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item.  So an example of the report would be the following:

Name | Available Qty
Kit A | 15
Kit B | 25
Item 4 | 100

How I can setup my report to group properly?

View 6 Replies View Related

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

Sep 21, 2006

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 1 Replies View Related

DELETE Items Where Count(items) &>1

May 12, 2006

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 10 Replies View Related

Hierarchical Design

Mar 27, 2008

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 3 Replies View Related

Hierarchical XML Import?

Apr 21, 2006


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 9 Replies View Related

Hierarchical Cumulative Sum

Aug 23, 2006

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 13 Replies View Related

TOP N Value Per Category

Oct 26, 2006

HelloI am using sql server 2005.I have two tables as described below.Table1UserID UserSales---------------------1 102 133 174 195 216 107 128 119 3110 2311 2412 1013 16Table2UserID Country----------------------1 Canada2 Canada3 Canada4 Canada5 Canada6 USA7 USA8 USA9 USA10 USA11 UK12 UK13 UKI want to get top 2 UserSales for each country and remaining should bedisplayed as Total as Others for that country.Can someone please help me with this query?RegardsAmit

View 1 Replies View Related

Hierarchical Table (count)

May 3, 2007

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 2 Replies View Related

Performing A Hierarchical Query...

Mar 26, 2008

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 3 Replies View Related

Hierarchical Query From Two Tables

Nov 17, 2014

I have created a store procedure as below:

WITH TextType AS
SELECT AppTxtTypeId,AppTxtTypeCode, AppTxtTypeParentCode, Name,Description,Active,SortOrder ,0 as TypeLevel,AppTxtTypeId as parentId
FROM [ApplicationTextTypes]
WHERE AppTxtTypeParentCode IS NULL

[Code] ....

From this i am able to get data in the below format:


Actually my requirement is :



View 1 Replies View Related

Fabricated Hierarchical Recordset

Jul 20, 2005

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 1 Replies View Related

Hierarchical Data Model

Sep 28, 2007


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 1 Replies View Related

Hierarchical Data In Result Set

May 1, 2006

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 5 Replies View Related

Hierarchical Resultset Sorting

May 25, 2006

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 1 Replies View Related

Flatten Hierarchical Data

Apr 17, 2008


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 5 Replies View Related

Import Hierarchical Data

Feb 17, 2006

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 11 Replies View Related

Breakdown By Category

Dec 22, 2005

Let's say you a 1000 records in the Employees table, who are spread over 40 different cities.
How would you get a breakdown of how many employees in each city ?

Do I have to loop with  a  Count(*)   for each CityID, or something ?

There must be a more straightforward method.

View 1 Replies View Related

Top 3 For Every Category With Group By

Mar 6, 2006


I have a table to store salesman's performance..

as you can see at structure.jpg, Tom sold total 18 Processors (5 different modals) and sold 11 mainboards (4 different modals)

I want to report that
what are the TOP 3 Processor and Mainboard modals that Tom sold with the same query

I tried different group by methods. also subqueries.
I could't do

you see the results I need at result.jpg

View 1 Replies View Related

HAVING (COUNT(category) &> 1) , Not Only 1 Row

Sep 25, 2006

i am using this code :

SELECT MAX(user) AS lastuser, category
FROM journal
GROUP BY category
HAVING (COUNT(category) > 1)

it works but returns 1 line by category >1

i need all the user (all the rows) HAVING (COUNT(category) > 1) , not only 1

if 1 category has only 1 user i must not keep it

i am not shure to be clear :-)

thank you for helping

View 8 Replies View Related

Copyrights 2005-15, All rights reserved