Search code examples
sqlmany-to-manyfrequency-analysisfrequency-distribution

SQL query: How to count frequency over a many-to-many relation?


I have a table for blogposts and a table for tags, with a many-to-many relation between them.

How can I extract the most frequent tag across a subset of blogposts? (e.g. only those from the past year) Is there a way to extract the frequencies of all the tags associated with the blogposts subset?

Thanks

Edit: my schema:

CREATE TABLE `tag` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(200) NOT NULL,
    `access` varchar(1),
    `linked_created` datetime
)
;

CREATE TABLE `blogpost_tags` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `blogpost_id` integer NOT NULL,
    `tag_id` integer NOT NULL,
    UNIQUE (`blogpost_id`, `tag_id`)
)
;

CREATE TABLE `blogpost` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `title` varchar(50),
    `body` varchar(500)
)
;

Solution

  • (Now that schema has been provided, removed my sample schema and just posted the query)

    Select T.name, Count(*) As UseCount
    From Tag As T
        Join BlogPost_Tag As BPT
            On BPT.tag_Id = T.Id
        Join BlogPost As BP
            On BP.Id = BPT.blogpost_id
    Where BP.Title Like '...'
    Group By T.name