CTE - a simpler solution?

Question

Post reply

CTE - a simpler solution?

as_1234

SSCrazy

Points: 2842

January 14, 2020 at 12:52 pm

#3715192

All,

I'm trying to get the average number of relatedpostid counts per postid. The following achieves the result I want:

create table postlinks (postid int ,relatedpostid int)

insert into postlinks(postid,relatedpostid) values (5,1)
insert into postlinks(postid,relatedpostid) values (5,2)
insert into postlinks(postid,relatedpostid) values(6,1)
insert into postlinks(postid,relatedpostid) values(6,2)
insert into postlinks(postid,relatedpostid) values(6,3)
insert into postlinks(postid,relatedpostid) values(6,4)


with postcount (postid,postcount) as 
(
select postid,count(relatedpostid) as postcount from postlinks
group by postid)

select avg(postcount) from postcount

However I'm wondering if there is a simpler way of getting the average that I've missed?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

Looks ok to me.

If you want non-integer averages you can do this by adding 0.0 to the count:

with postcount (postid,postcount) as 
(
select postid,count(relatedpostid)+0.0 as postcount 
  from postlinks
 group by postid
)
select avg(postcount) 
  from postcount

Phil Parkin SSC Guru Points: 246695 More actions · Answer 2

Does this work?

SELECT COUNT(*) / COUNT(DISTINCT p.postid)
FROM postlinks p;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

as_1234 SSCrazy Points: 2842 More actions · Answer 3

Both,

Thank you for your help.

Jonathan: Thanks, I hadn't realised it had rounded to an int. Am I correct in thinking that AVG will return an int if it receives an int and a decimal if it receives a decimal? Having seen your post I seem to remember that it works like that but I couldn't find a reference online to check.

Phil: Thanks. I think it is the same, although my maths is not brilliant! It also returns an int rather than a decimal. I'm currently working on trying to make it a decimal, if I work it out I'll post incase it helps others.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 4

Returning a decimal is easy:

SELECT CAST(COUNT(*) AS DECIMAL(19,6)) / COUNT(DISTINCT p.postid)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Mike01 SSChampion Points: 11393 More actions · Answer 5

It is returning an integer because count returns an integer value. By adding the 0.0 to it, it does an implicit conversion to decimal.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

as_1234 SSCrazy Points: 2842 More actions · Answer 6

as_1234

SSCrazy

Points: 2842

January 15, 2020 at 10:38 am

#3715549

All,

Thanks for your help.