none
Count RRS feed

  • Question

  • Hello,

    I have 3 tables:

    Tags (TagdId, Name)

    TagsPosts (TagId, PostId)

    TagsFiles (TagId, FileId)

    I need to find how many of the tags in Tags are associated to Posts or Files, i.e., how many of them appear in TagsPosts or TagsFiles ... Of course if a Tag appears twice or more in either TagsPosts, TagsFiles or both I want only to be counted once.

    Basically, I need to find the share of tags that are associated to either a post, a file or both:

    Share = ???? / database.Tags.Count() * 100;

    I have all relations of LinqToSQL tables created so I can use:

    database.Tags.Count(t = > t.PostsTags ...

    How can I do this?

    Thanks,
    Miguel


    Saturday, October 18, 2008 11:16 PM

Answers

  • int tagsInPosts = (from p in db.TagsPosts select p.TagId).Distinct().Count();

     

    int tagsInFiles = (from f in db.TagsFiles select f.TagId).Distinct().Count();

     

    int tagsInEither =

                   (from p in db.TagsPosts select p.TagId)

                   .Union(from f in db.TagsFiles select f.TagId)

                   .Count();

     

    int tagsInBoth = (from p in db.TagsPosts select p.TagId)

                            .Intersect(from f in db.TagsFiles select f.TagId)

                            .Count();

     

    int totalTags = db.Tags.Count();

     

    double shareInPosts = (tagsInPosts / (double)totalTags)  * 100;

     

    double shareInFiles = (tagsInFiles / (double)totalTags) * 100;

     

    double shareInEither (tagsInEither / (double)totalTags) * 100;

     

    double shareInBoth = (tagsInBoth / (double)totalTags) * 100;

     

     

     

     

     

     

    Sunday, October 19, 2008 12:46 AM
    Moderator

All replies

  • int tagsInPosts = (from p in db.TagsPosts select p.TagId).Distinct().Count();

     

    int tagsInFiles = (from f in db.TagsFiles select f.TagId).Distinct().Count();

     

    int tagsInEither =

                   (from p in db.TagsPosts select p.TagId)

                   .Union(from f in db.TagsFiles select f.TagId)

                   .Count();

     

    int tagsInBoth = (from p in db.TagsPosts select p.TagId)

                            .Intersect(from f in db.TagsFiles select f.TagId)

                            .Count();

     

    int totalTags = db.Tags.Count();

     

    double shareInPosts = (tagsInPosts / (double)totalTags)  * 100;

     

    double shareInFiles = (tagsInFiles / (double)totalTags) * 100;

     

    double shareInEither (tagsInEither / (double)totalTags) * 100;

     

    double shareInBoth = (tagsInBoth / (double)totalTags) * 100;

     

     

     

     

     

     

    Sunday, October 19, 2008 12:46 AM
    Moderator
  • Thank You!
    Sunday, October 19, 2008 1:05 AM