SQL Query Group By - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query Group By

Author

Topic

khanewal
Starting Member

33 Posts

Posted - 2010-09-20 : 01:50:01

Doc_id ref
1 null
2 null
3 1
4 1
5 2
Required Results:
Doc_ID total
1 2
2 1

how I can achieve these results ?

many thanks,

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-20 : 02:00:19

Try this:

Select ref as doc_id, count(1) from yourtable
group by ref

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

PavanKK
Starting Member

32 Posts

Posted - 2010-09-20 : 02:23:51

Hi Khanewal,

If am not wrong you are trying to get the total recursive count of all children under a parent node. If that is the case you can try the following. If am wrong let us know in detail to help you better.

-----

DECLARE @T TABLE (Doc_id INT, ref INT)
INSERT @t VALUES(1,null)
INSERT @t VALUES(2,null)
INSERT @t VALUES(3,1)
INSERT @t VALUES(4,1)
--INSERT @t VALUES(6,3)
INSERT @t VALUES(5,2)

;WITH Cte AS
(
SELECT t2.Doc_id,t2.ref,t1.Doc_id AS Parent
FROM @T t1
JOIN @T t2 ON t1.Doc_id = t2.ref AND t1.ref IS NULL

UNION ALL

SELECT t.Doc_id,t.ref,C.Parent
FROM @T t
JOIN Cte c ON C.Doc_id = t.ref

)

SELECT Parent AS Doc_ID,COUNT(Doc_id) AS Total
FROM Cte
GROUP BY Parent

-------

KK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources