# CTE - a simpler solution?

• as1981

SSCrazy

Points: 2745

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 postlinksgroup 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?

• Jonathan AC Roberts

SSCoach

Points: 17319

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: 244656

Does this work?

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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

• as1981

SSCrazy

Points: 2745

Both,

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: 244656

Returning a decimal is easy:

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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

• Mike01

SSChampion

Points: 11282

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/

• as1981

SSCrazy

Points: 2745

All,