Count child articles for child categories

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)

Count child articles for child categories

Author

Topic

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-09-13 : 13:13:34

I have a problem!

I have my categories table with:
CategoryID, ParentID, Name

And my articles table: ArticleID, CategoryID, Name.... etc.

I need to select "CategoryID, CategoryName, ArticlesCount"

"articlescount" Should represent the count for all articles in this category and the count for all articles in childs categorys

I tried but I don't know how can i build my query! :(

Thanks,
Max

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 13:35:54

SELECT p.CatID, COUNT(*) FROM Parent p JOIN Aritcles a ON p.CatID = a.CatID GROUP by p.Catid

????????????????????????????????????????????????????????????????????????????????????????????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-09-13 : 14:39:42

No... That query only show the article count for each category.
I want cumulative count!

I tried your example:


CREATE TABLE Categories(ID int, Name varchar(50), ParentID int)
CREATE TABLE Articles(ID int, Name varchar(50), CategoryID int)
INSERT INTO [Categories] ([Name],[ParentID]) VALUES (C1, 0)
INSERT INTO [Categories] ([Name],[ParentID]) VALUES (C2, 1)
INSERT INTO [Categories] ([Name],[ParentID]) VALUES (C3, 1)
INSERT INTO [Categories] ([Name],[ParentID]) VALUES (C4, 3)
INSERT INTO [Categories] ([Name],[ParentID]) VALUES (C5, 3)

INSERT INTO [Articles] ([Name],[CategoryID]) VALUES (A1, 3)
INSERT INTO [Articles] ([Name],[CategoryID]) VALUES (A2, 3)
INSERT INTO [Articles] ([Name],[CategoryID]) VALUES (A3, 5)
INSERT INTO [Articles] ([Name],[CategoryID]) VALUES (A4, 5)
INSERT INTO [Articles] ([Name],[CategoryID]) VALUES (A5, 5)
INSERT INTO [Articles] ([Name],[CategoryID]) VALUES (A6, 4)
INSERT INTO [Articles] ([Name],[CategoryID]) VALUES (A7, 4)
INSERT INTO [Articles] ([Name],[CategoryID]) VALUES (A8, 4)

SELECT c.ID, COUNT(*) FROM Categories c JOIN Articles a ON c.ID = a.CategoryID GROUP by c.ID

With this string that you gave me i select:
ID - Count
3 - 2
4 - 3
5 - 3

But I want to select cumulative count, like:
ID - Count
3 - 8
4 - 3
5 - 3

The next image demonstrate the structure:

category3 (c3) has 8 articles! Not 2 as your query return!

If anyone could help me i'll appreciate

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 14:43:13

It's called a hierarchy

And what value is taht?

In any case, are you using SQL Server 2k5?

You will need to look up CTE's if you are, or you will need a UDF or CURSOR

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 14:46:54

[code]DECLARE @Sample TABLE (ID INT, ParentID INT, Name VARCHAR(100), Amount MONEY)

INSERT @Sample
SELECT 7, NULL, 'Ljunggren', 1000 UNION ALL
SELECT 3, 1, 'Gulli', 200 UNION ALL
SELECT 8, 7, 'Kerstin', 45 UNION ALL
SELECT 1, NULL, 'Rosberg', 1 UNION ALL
SELECT 4, 2, 'Peter', 222 UNION ALL
SELECT 5, 3, 'Susanne', 111 UNION ALL
SELECT 2, 1, 'Jan-Eric', 59 UNION ALL
SELECT 10, 9, 'Jennie', 2000 UNION ALL
SELECT 6, 3, 'Annelie', 666 UNION ALL
SELECT 9, 7, 'Kenneth', 100 UNION ALL
SELECT 11, 9, 'Jessica', 199

;WITH Yak (Level, ID, ParentID, Name, Amount)
AS (
SELECT 0,
s1.ID,
s1.ParentID,
s1.Name,
s1.Amount
FROM @Sample AS s1
LEFT JOIN @Sample AS s2 ON s2.ParentID = s1.ID
WHERE s2.ID IS NULL

UNION ALL

SELECT y.Level + 1,
s.ID,
s.ParentID,
s.Name,
s.Amount + y.Amount
FROM Yak AS y
INNER JOIN @Sample AS s ON s.ID = y.ParentID
)

SELECT ID,
Name,
SUM(Amount) AS Amount
FROM Yak
GROUP BY ID,
ParentID,
Name
ORDER BY ID,
ParentID,
Name[/code]

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 14:53:36

You must be bored

There are no local pubs with a dart board?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 14:57:07

Nope. Not within walking distance anyway.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 15:18:26

Better version

DECLARE	@Sample TABLE (ID INT, ParentID INT, Name VARCHAR(100), Amount INT)

INSERT	@Sample
SELECT	7, NULL, 'Ljunggren', 200 UNION ALL
SELECT	3, 1, 'Gulli', 199 UNION ALL
SELECT	8, 7, 'Kerstin', 23 UNION ALL
SELECT	1, NULL, 'Rosberg', 145 UNION ALL
SELECT	4, 2, 'Peter', 222 UNION ALL
SELECT	5, 3, 'Susanne', 333 UNION ALL
SELECT	2, 1, 'Jan-Eric', 234 UNION ALL
SELECT	10, 9, 'Jennie', 333 UNION ALL
SELECT	6, 3, 'Annelie', 666 UNION ALL
SELECT	9, 7, 'Kenneth', 1200 UNION ALL
SELECT	11, 9, 'Jessica', 69

;WITH Yak (ID, ParentID, Name, Path, Indent, Amount)
AS (
	SELECT	ID,
		ParentID,
		Name,
		CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY Name)),
		0,
		Amount
	FROM	@Sample
	WHERE	ParentID IS NULL

	UNION ALL

	SELECT		s.ID,
			s.ParentID,
			s.Name,
			CONVERT(VARCHAR, y.Path + '.' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY s.Name DESC))),
			y.Indent + 1,
			s.Amount
	FROM		@Sample AS s
	INNER JOIN	Yak AS y ON y.ID = s.ParentID
)

SELECT		y1.ID,
		y1.ParentID,
		REPLICATE('.....', y1.Indent) + y1.Name AS Name,
		y1.Path,
		y1.Amount,
		SUM(y2.Amount) AS TotAmount
FROM		Yak AS y1
INNER JOIN	Yak AS y2 ON y2.Path LIKE y1.Path + '%'
GROUP BY	y1.ID,
		y1.ParentID,
		REPLICATE('.....', y1.Indent) + y1.Name,
		y1.Amount,
		y1.Path
ORDER BY	y1.Path

E 12°55'05.25"
N 56°04'39.16"

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-09-13 : 16:01:44

Peso, you should delete your first script...

I tested and i was here trying to understand why did he return wrong cumulative sum's...

Thanks for posting version 2! This work!

But i have one question (forgive me)
I have two tables.
Categories(with parentcategoryid) and articles.
I need to select all categories(with articlescount from articlesTable) into one tempTable and only then run your procedure right?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 16:07:09

Or, with the concept of JOIN, you can add the second column directly in the CTE.

E 12°55'05.25"
N 56°04'39.16"

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-09-13 : 16:35:23

Thanks Very much Peso! I'm glad that you had time for this. I searched in this forum and i found many post asking for this without answers !

I'm going to have 2 tables:


Declare @Categories TABLE (CategoryID int IDENTITY(1,1), Name varchar(50), ParentID int)
Declare @Articles TABLE (ArticleID int IDENTITY(1,1), Name varchar(50), CategoryID int)
INSERT INTO @Categories ([Name],[ParentID]) VALUES ('C1', 0)
INSERT INTO @Categories ([Name],[ParentID]) VALUES ('C2', 1)
INSERT INTO @Categories ([Name],[ParentID]) VALUES ('C3', 1)
INSERT INTO @Categories ([Name],[ParentID]) VALUES ('C4', 3)
INSERT INTO @Categories ([Name],[ParentID]) VALUES ('C5', 3)

INSERT INTO @Articles ([Name],[CategoryID]) VALUES ('A1', 3)
INSERT INTO @Articles ([Name],[CategoryID]) VALUES ('A2', 3)
INSERT INTO @Articles ([Name],[CategoryID]) VALUES ('A3', 5)
INSERT INTO @Articles ([Name],[CategoryID]) VALUES ('A4', 5)
INSERT INTO @Articles ([Name],[CategoryID]) VALUES ('A5', 5)
INSERT INTO @Articles ([Name],[CategoryID]) VALUES ('A6', 4)
INSERT INTO @Articles ([Name],[CategoryID]) VALUES ('A7', 4)
INSERT INTO @Articles ([Name],[CategoryID]) VALUES ('A8', 4)



DECLARE	@Sample TABLE (ID INT, ParentID INT, Name VARCHAR(50), ArticlesCount INT)

INSERT	@Sample
SELECT CategoryID, ParentID, Name, (SELECT COUNT(*) FROM @Articles AS a WHERE a.categoryID = c.CategoryID) As ArticlesCount
   FROM @Categories AS c
   ORDER BY Name ASC

;WITH Yak (ID, ParentID, Name, Path, Indent, ArticlesCount)
AS (
	SELECT	ID,
		ParentID,
		Name,
		CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY Name)),
		0,
		ArticlesCount
	FROM	@Sample
	WHERE	ParentID = 0 --IS NULL

	UNION ALL

	SELECT		s.ID,
			s.ParentID,
			s.Name,
			CONVERT(VARCHAR, y.Path + '.' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY s.Name DESC))),
			y.Indent + 1,
			s.ArticlesCount
	FROM		@Sample AS s
	INNER JOIN	Yak AS y ON y.ID = s.ParentID
)

SELECT		y1.ID,
		y1.ParentID,
		REPLICATE('.....', y1.Indent) + y1.Name AS Name,
		y1.Path,
		y1.ArticlesCount,
		SUM(y2.ArticlesCount) AS TotArticles
FROM		Yak AS y1
INNER JOIN	Yak AS y2 ON y2.Path LIKE y1.Path + '%'
GROUP BY	y1.ID,
		y1.ParentID,
		REPLICATE('.....', y1.Indent) + y1.Name,
		y1.ArticlesCount,
		y1.Path
ORDER BY	y1.Path

I want this for articles and categories, but I need to upgrade my asp.net forum with subforums...
I'm thinking about use this code!
When user enter in forum, the parent forums need to show all posts Cumulative count.
But i have more than thousands of posts... Is this a good solution for that?

Thanks very much!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 17:05:57

[code]-- Prepare sample data
DECLARE @Categories TABLE (CategoryID INT, Name VARCHAR(50), ParentID INT)

INSERT @Categories (CategoryID, Name, ParentID) VALUES (1, 'C1', 0)
INSERT @Categories (CategoryID, Name, ParentID) VALUES (2, 'C2', 1)
INSERT @Categories (CategoryID, Name, ParentID) VALUES (3, 'C3', 1)
INSERT @Categories (CategoryID, Name, ParentID) VALUES (4, 'C4', 3)
INSERT @Categories (CategoryID, Name, ParentID) VALUES (5, 'C5', 3)

DECLARE @Articles TABLE (ArticleID INT, Name VARCHAR(50), CategoryID INT)

INSERT @Articles (ArticleID, Name, CategoryID) VALUES (1, 'A1', 3)
INSERT @Articles (ArticleID, Name, CategoryID) VALUES (2, 'A2', 3)
INSERT @Articles (ArticleID, Name, CategoryID) VALUES (3, 'A3', 5)
INSERT @Articles (ArticleID, Name, CategoryID) VALUES (4, 'A4', 5)
INSERT @Articles (ArticleID, Name, CategoryID) VALUES (5, 'A5', 5)
INSERT @Articles (ArticleID, Name, CategoryID) VALUES (6, 'A6', 4)
INSERT @Articles (ArticleID, Name, CategoryID) VALUES (7, 'A7', 4)
INSERT @Articles (ArticleID, Name, CategoryID) VALUES (8, 'A8', 4)

-- Stage some data
;WITH Yak (CategoryID, Name, ParentID, Articles)
AS (
SELECT c.CategoryID,
c.Name,
c.ParentID,
COUNT(a.ArticleID)
FROM @Categories AS c
LEFT JOIN @Articles AS a ON a.CategoryID = c.CategoryID
GROUP BY c.CategoryID,
c.Name,
c.ParentID
), Yak2 (CategoryID, Name, ParentID, Articles, Path)
AS (
SELECT CategoryID,
Name,
ParentID,
Articles,
CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY CategoryID))
FROM Yak
WHERE ParentID = 0

UNION ALL

SELECT y.CategoryID,
y.Name,
y.ParentID,
y.Articles,
CONVERT(VARCHAR, y2.Path + '.' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY y.CategoryID)))
FROM Yak AS y
INNER JOIN Yak2 AS y2 ON y2.CategoryID = y.ParentID
)

-- Show the expected output
SELECT y1.CategoryID,
y1.Name,
y1.Path,
y1.Articles,
SUM(y2.Articles) AS TotArticles
FROM Yak2 AS y1
INNER JOIN Yak2 AS y2 ON y2.Path LIKE y1.Path + '%'
GROUP BY y1.CategoryID,
y1.Name,
y1.Path,
y1.Articles
ORDER BY y1.Path[/code]

E 12°55'05.25"
N 56°04'39.16"

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-09-13 : 18:13:53

:) Thanks Peso! I had hope that you could rectify the original code! Thanks a lot.

My ability is asp.net... I use sql for the basic stuff. But I'm concerned with rapid queries...

I'll use this script to select my forums and one-level sub-forums too!

Max,
Portugal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources