Record counts from the table
-
Hi,
I need to count the different total number of records from the table as table has column which contains records in hierarchic way.
Ex. Column name: CatName, which contains data like Catalog, Doc, Folder, Files. When it's design into portal as below.
Catalog ==> IT, HR, FIN, Legal
Now under IT, it has another Folder and Document and files like: Network, DR and each contains files
So I need to count how many IT records then under IT, how many Folder, then under each folder , how many files.
I hope i have clear instruction what i want it,.
-
So Kind of Hierarchic is like as shown in image (It's not still perfect)
-
Hello, if I understand you need somethink like this :
DECLARE @MyCatalog TABLE(
[idtable] int NOT NULL,
[idparent] int,
[nameCatalog] varchar(50)
);
INSERT INTO @MyCatalog VALUES(1,NULL,'CORP');
INSERT INTO @MyCatalog VALUES(2,1,'IT');
INSERT INTO @MyCatalog VALUES(3,1,'HR');
INSERT INTO @MyCatalog VALUES(4,1,'FIN');
INSERT INTO @MyCatalog VALUES(5,1,'LEGAL');
INSERT INTO @MyCatalog VALUES(6,2,'DOC1');
INSERT INTO @MyCatalog VALUES(7,2,'DOC2');
INSERT INTO @MyCatalog VALUES(8,2,'DOC3');
INSERT INTO @MyCatalog VALUES(9,6,'FILES1');
INSERT INTO @MyCatalog VALUES(10,7,'FILES2');
INSERT INTO @MyCatalog VALUES(11,8,'FILES3');
INSERT INTO @MyCatalog VALUES(12,8,'FILES4');
INSERT INTO @MyCatalog VALUES(13,3,'DOC4');
INSERT INTO @MyCatalog VALUES(14,3,'DOC5');
INSERT INTO @MyCatalog VALUES(15,3,'DOC6');
INSERT INTO @MyCatalog VALUES(16,4,'DOC7');
;WITH CTchildrenCOUNT
AS (
SELECT
[idtable]
,[idparent]
,[nameCatalog]
FROM
@MyCatalog
WHERE
idparent IS NULL
UNION ALL
SELECT
child.[idtable]
,child.[idparent]
,child.[nameCatalog]
FROM
@MyCatalog child
INNER JOIN
CTchildrenCOUNT parent ON child.idparent=parent.[idtable]
)
,CTparentCOUNT
AS(
SELECT
ref.idtable
,ref.idparent
,ref.nameCatalog
,isnull(totalCurrent.children,0) totalCurrent
FROM
@MyCatalog ref
LEFT JOIN
(
SELECT
idparent
,COUNT(*) children
FROM
CTchildrenCOUNT
GROUP BY
idparent
) totalCurrent
ON
totalCurrent.idparent=ref.idtable
)
SELECT
idtable,
idparent,
nameCatalog,
totalCurrent,
(
SELECT
ISNULL(sum(totalCurrent),0)
FROM
CTparentCOUNT B
WHERE
A.idtable=isnull(B.idparent,1)
)+totalCurrent AS totalChildren
FROM CTparentCOUNT A - pdsqsql wrote:
So Kind of Hierarchic is like as shown in image (It's not still perfect)
Two questions...
- How many total rows do you have in this hierarchy?
- How often do you add rows to the hierarchy?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Wow! I think this will work.
This is a OpenText software document. Rows will be added randomly, not fix.
I will check total rows and let you know.
I need to count by year for every Doc/Folder/Catalog meand for each in hierarchy
Thanks for your response.Really appriciate!
- pdsqsql wrote:
Jeff,
Wow! I think this will work.
This is a OpenText software document. Rows will be added randomly, not fix.
I will check total rows and let you know.
I need to count by year for every Doc/Folder/Catalog meand for each in hierarchy
Thanks for your response.Really appriciate!
You're giving credit to the wrong person. It was Med Anwar that posted the code... not me.
I posted a couple of questions because there's a nasty fast way to do all of this an more depending on the answers you provide.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Med Anwar for your help!
WE have total records around 25k.
When i look into OpenText web, it shows following:
I need to generate report like following it shows into web, but more in depth for each hierarchic
CORP - Main Folder
Total CountsTotal Counts
CorpDocumentsFolders
Communications1068
Dev71
Eng03
FIN308119
HR105
IT13033
Audit 105
Legal 195
Sales 186
Shipping 201 -
Thanks Anwar for your very good help!
I am little confused when i was looking my data and try to use your query.
I have main ParentId, if i pass parentID then i get the list of my all main catalogs their name store with DataID reference to Main ParentId,
Ex.
ParentID NAME DATAID
1 IT 11
1 FIN 22
1 HR 33
1 Sales 44
1 Legal 55each catalog have different Folders so those different folders have different DataID tiedup with their ParentID
Ex.
ParentID NAME DATAID
11 IT Ploicy 11-1
11 IT Procedure 11-2
11 IT Deployment 11-3 (there are more Folder/documents)
22 Financial report 22-1
22 Financial stmnts 22-2
22 Financial Release 22-3
22 Financial policy 22-4 (there are more Folder/documents)
33 HR Process 33-2
33 HR Process 33-3
33 HR Process 33-4 (there are more Folder/documents)
(there are more Folder/documents)
Now these 22-, 22-2, ..., 33-2, 33-2, 33-3 has also contains Folders/Documents
So it will be like
11-1 IT Network Policy 11-1A
11-2 IT Database Policy 11-2A
11-3 IT Deployment 11-3A (there are more Folder/documents)
Same way FIN, HR and ...etc
then another level ex. for IT again
11-1A IT N/W Policy configuration 11-1A_1
11-2A IT DB Maint Policy 11-2A_1
(there are more Folder/documents)
We are storing documents like .img, .doc, .txt, .pdf etc
My main columns are PArentID,DataID, name etc... -
You are welcome, the first code will not work if you have too much level hierarchie .
For your structure you can try the following code, let me know if it works like you want.
DECLARE @MyCatalog TABLE(
[ParentID] varchar(500),
[NAME] varchar(500),
[DATAID] varchar(500)
);
INSERT INTO @MyCatalog VALUES(NULL,'CORP','1');
INSERT INTO @MyCatalog VALUES('1','IT','11');
INSERT INTO @MyCatalog VALUES('1','FIN','22');
INSERT INTO @MyCatalog VALUES('1','HR','33');
INSERT INTO @MyCatalog VALUES('1','Sales','44');
INSERT INTO @MyCatalog VALUES('1','Legal','55');
INSERT INTO @MyCatalog VALUES('11','IT Ploicy','11-1');
INSERT INTO @MyCatalog VALUES('11','IT Procedure','11-2');
INSERT INTO @MyCatalog VALUES('11','IT Deployment','11-3');
INSERT INTO @MyCatalog VALUES('22','Financial report','22-1');
INSERT INTO @MyCatalog VALUES('22','Financial stmnts','22-2');
INSERT INTO @MyCatalog VALUES('22','Financial Release','22-3');
INSERT INTO @MyCatalog VALUES('22','Financial policy','22-4');
INSERT INTO @MyCatalog VALUES('33','HR Process','33-2');
INSERT INTO @MyCatalog VALUES('33','HR Process','33-3');
INSERT INTO @MyCatalog VALUES('33','HR Process','33-4');
INSERT INTO @MyCatalog VALUES('11-1','IT Network Policy','11-1A');
INSERT INTO @MyCatalog VALUES('11-2','IT Database Policy','11-2A');
INSERT INTO @MyCatalog VALUES('11-3','IT Deployment 11-3A','11-3A');
INSERT INTO @MyCatalog VALUES('11-1A','IT N/W Policy configuration','11-1A_1');
INSERT INTO @MyCatalog VALUES('11-2A','IT DB Maint Policy','11-2A_1');
;WITH CTchild
AS(
SELECT
cat.DATAID
,cat.ParentID
,cat.NAME
,isnull(totalCurrent.children,0) totalCurrent
FROM
@MyCatalog cat
LEFT JOIN
(
SELECT
ParentID
,COUNT(*) children
FROM
@MyCatalog
GROUP BY
ParentID
) totalCurrent
ON
totalCurrent.ParentID =cat.DATAID
)
,CTparent as
(
select [DATAID],
totalcurrent,
[DATAID] as ctParentID
from CTchild
union all
select CTchild.[DATAID],
CTchild.totalcurrent ,
CTparent.ctParentID
from CTchild
inner join CTparent
on CTchild.[ParentID] = CTparent.[DATAID]
)
select CTchild.DATAID,
CTchild.ParentID,
CTchild.Name,
CTchild.totalcurrent,
SumCTparent.totalChildren
from CTchild
inner join (
select ctParentID,
sum(totalcurrent) as totalChildren
from CTparent
group by ctParentID
) as SumCTparent
on CTchild.DATAID = SumCTparent.ctParentID
order by CTchild.DATAID
option (maxrecursion 0) -
Lordy... I'll never understand why people nest the extended structure of the hierarchy in the parent and child IDs. Just imagine the fur that will fly if you ever need to add another intermediate level between existing levels or if something like IT suddenly gets reorganized to get rid of some sub areas and have others added and the existing leaf level data needs to be redistributed to accommodate that. And imagine if requirement pops up to store document info a different levels instead of all one level like you currently have.
And, as you're finding out, producing aggregates at different levels is a real PITA.
You answered one of my questions (how many nodes in the hierarchy) but you've not answered the other question... How often do you add documents/nodes to this hierarchy?
Seeing the content of the "DataID" column, I'll ask another question... any interest in fixing this hierarchy to be more flexible, easier to do calculations/aggregations with, and more scalable or are you pretty much stuck with having to do it this way?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Anvar for another code example to cover more level of hierarchie.
I will try and let you know.
Once again thank you very much for your great help!
Appreciate it!
-
Jeff,
I am completely agreed with you but this is a OPENTEXT application software so table design we don't have any control.
Honestly, I don't know how frequently we are adding the documents as i am new but definitely i am with you the way it's design and it will more difficult when it will go further more and more level.
It's 3rd party software so we are stuck.
Looks like Sharepoint has much more capability and easier to produce the results.
Thanks for your efforts to helping me and community.
- Jeff Moden wrote:
How often do you add documents/nodes to this hierarchy?
According to the OP there are approximately 25k rows in the table. If this represents 5 years of activity, then roughly 5k per year.
Jeff Moden wrote:Seeing the content of the "DataID" column, I'll ask another question... any interest in fixing this hierarchy to be more flexible, easier to do calculations/aggregations with, and more scalable or are you pretty much stuck with having to do it this way?
The OP has access to the table so it could be transformed as necessary. Would it help to normalize the id values into numeric pk's?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- pdsqsql wrote:
Jeff,
I am completely agreed with you but this is a OPENTEXT application software so table design we don't have any control.
Honestly, I don't know how frequently we are adding the documents as i am new but definitely i am with you the way it's design and it will more difficult when it will go further more and more level.
It's 3rd party software so we are stuck.
Looks like Sharepoint has much more capability and easier to produce the results.
Thanks for your efforts to helping me and community.
Ok. I think this can still be helped quite a bit, even without knowing the frequency of moves, adds, and deletes. I'll try to get back on this.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Steve Collins wrote:Jeff Moden wrote:
How often do you add documents/nodes to this hierarchy?
According to the OP there are approximately 25k rows in the table. If this represents 5 years of activity, then roughly 5k per year.
Jeff Moden wrote:Seeing the content of the "DataID" column, I'll ask another question... any interest in fixing this hierarchy to be more flexible, easier to do calculations/aggregations with, and more scalable or are you pretty much stuck with having to do it this way?
The OP has access to the table so it could be transformed as necessary. Would it help to normalize the id values into numeric pk's?
I couldn't assume such a thing for possible frequency. We work with documents and we go through 50,000 documents per day. The table size never changes because incoming is almost a perfect daily match for outgoing.
Also, the OP just stated that we can't do a thing with the table directly nor the DataID because it IS a part of a 3rd party product. That's why I always ask the OP on such things and generally ignore my own presumptions as well as those of others. People's situations vary so much that I just can't assume even if some assumptions seem totally obvious, especially in light of the very poor form that the DataID column is in.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply