CTE - a simpler solution?

Question

Post reply

CTE - a simpler solution?

as_1234

SSCrazy

Points: 2859

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: 18354 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: 246960 More actions · Answer 2

Does this work?

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

as_1234 SSCrazy Points: 2859 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: 246960 More actions · Answer 4

Returning a decimal is easy:

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Mike01 SSChampion Points: 11395 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: 2859 More actions · Answer 6

as_1234

SSCrazy

Points: 2859

January 15, 2020 at 10:38 am

#3715549

All,

Thanks for your help.