help with count query - 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 2000 Forums

Transact-SQL (2000)

help with count query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-03-28 : 17:37:35

Hi,

I have a forum just like this SQLTeam forum. I need to have a count(*) of all the pages, which is equal to a count of all the topics +1 for every topic that has more than 20 replies. The query below is an example with data brought back.

How can I incorporate the 20 reply factor into the count?

Any help is much appreciated..

thanks again
mike123

SELECT topic_ID, t_replies FROM FORUM_TOPICS ORDER BY topic_ID desc

607199 8
607198 11
607197 3
607196 3
607195 9
607194 52
607193 1
607192 3
607191 0
607190 43
607189 11
607188 38
607187 21

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-28 : 17:56:13

Is this what you want?

SELECT topic_ID, COUNT(*)
FROM FORUM_TOPICS
GROUP BY topic_ID
HAVING COUNT(*) > 20

If it isn't, we are going to need more information.

Tara Kizer
aka tduggan

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-03-28 : 18:16:43

Hi Tara

,

Sorry for the bad explanation.. I think I got something better here.

Each row below represents a topic_ID and the amount of replies. It's a forum just like this board. Each topic has 20 replies per page, so 22 replies = 2 pages, 100 replies = 5 pages, 101 replies = 6 pages etc.

I need the total page count. The reason I need this is for links to the actual topic threads, and x pages deep in replies.

Hopefully thats more clear. Let me know if I can provide any other info.

Thanks again!

topic_ID/ t_replies

607199 8 (1 topic with 8 replies = 1 page)
607198 11 (1 topic with 11 replies = 1 page)
607197 3 (1 topic with 3 replies = 1 page)
607196 3 (1 topic with 3 replies = 1 page)
607195 9 (1 topic with 9 replies = 1 page)
607194 52 (1 topic with 52 replies = 3 pages)
607193 1 (1 topic with 1 replies = 1 page)
607190 43 (1 topic with 43 replies = 3 pages)

The result I would like back is "12" for the 8 records above.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-28 : 18:19:11

Please post DDL, INSERT INTO statements for sample data, and the expected result set.

Tara Kizer
aka tduggan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 18:25:12

declare	@table	table
	(
		topic		int,
		replies		int
	)
insert into @table
select 607199,  8	union all
select 607198, 11	union all
select 607197, 3	union all
select 607196, 3	union all
select 607195, 9	union all
select 607194, 52	union all
select 607193, 1	union all
select 607192, 3	union all
select 607191, 0	union all
select 607190, 43	union all
select 607189, 11	union all
select 607188, 38	union all
select 607187, 21

select 	sum(no_pages) as total_pages
from
(
	select	topic, replies, (replies / 20) + 1 as no_pages
	from	@table
) p

select 	sum((replies / 20) + 1) as total_pages
from	@table

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources