CTE - a simpler solution?

  • as1981

    SSCrazy

    Points: 2565

    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?

  • Jonathan AC Roberts

    SSCoach

    Points: 17087

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

    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.

  • as1981

    SSCrazy

    Points: 2565

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

    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.

  • Mike01

    SSChampion

    Points: 11093

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

    All,

    Thanks for your help.

     

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

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