Count Number of Documents per container 1

[Boham]

Please Help!!!

I have created a LiveReport which allows me to select a container and it will list all of the UNIQUE document names within the container. I knwo that I have duplicate documents within the container which I need to include... What I really need is a count where subtype = "144", "557" and "751". The code I have currently is:

SELECT dtree.name "Document Name", dtree.versionnum "Number Versions"FROM DTREE,kuaf where dtree.dataid in (SELECT dtree.DATAID FROM DTREE START WITH dtree.DATAID = %1 CONNECT BY PRIOR dtree.DATAID = dtree.PARENTID)and dtree.versionnum > 0

Your Help is GREATLY APPRECIATED

Bobby

 

[ggriffit]

IF you just want the subtypes then you can simply change the SQL to :

SELECT dtree.name "Document Name", dtree.versionnum "Number Versions"FROM DTREE,kuaf where dtree.dataid in (SELECT dtree.DATAID FROM DTREE START WITH dtree.DATAID = %1 CONNECT BY PRIOR dtree.DATAID = dtree.PARENTID)and (dtree.versionnum > 0) AND dtree.subtype in (144,557,751)

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[Boham]

I actually would like to change it so that maybe it give me one of the following:

subtype 144 = 500
subtype 557 = 200
subtype 751 = 100
total = 800

or

Total Count = 800

However; I am giving your recommendation a whirl just because time is of the essence on this project.

I appreciate your help thus far and if you have any pointers on modifying it to give either of the above scenarios I would be greatful.

Bobby

 

[ggriffit]

Then I guess you'll want a GROUP BY, something like :

SELECT dtree.subtype "SubType",count(*) "Number"
FROM DTREE
WHERE dtree.subtype in (144,557,751)
AND (dtree.versionnum > 0)
AND dtree.dataid in
(SELECT dtree.dataid
FROM DTREE
START WITH dtree.DATAID = %1
CONNECT BY PRIOR dtree.DATAID = dtree.PARENTID)
GROUP BY dtree.subtype

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[Boham]

Greg,

Thank you SOOOOOOO much! I edited my report and it works perfectly.... You have save me a lot of data manipulation in excel!

Bobby