CODEWITHSUNDEEP
mysqlsql
Mike Thomsen
Mike Thomsen

Reputation: 37506

SQL group by over two joins

These are my tables:

create table articles(
    id integer auto_increment,
    site varchar(64) not null,
    title varchar(512) not null,
    link varchar(512) not null,
    pub_date datetime not null,
    primary key(id)
);

create table entities (
    id integer auto_increment,
    entity varchar(1024) not null,
    entity_type varchar(32) not null,
    primary key(id)
);

create table articles_entities (
    article_id integer not null,
    entity_id integer not null,
    foreign key(entity_id) references entities(id),
    foreign key(article_id) references articles(id)
);

This is the starting point of what I am trying to do:

select count(*), entity_type from entities group by entity_type;

I obviously get something that looks like this:

+----------+---------------+
| count(*) | entity_type   |
+----------+---------------+
|    15418 | locations     |
|    21789 | misc          |
|    62306 | organizations |
|   121307 | people        |
+----------+---------------+

What I want is something like this:

+----------+---------------+------+
| count(*) | entity_type   | site |
+----------+---------------+------+
|    15418 | locations     | ABC  |
|    21789 | misc          | ABC  |
|    62306 | organizations | ABC  |
|   121307 | people        | ABC  |
|    13418 | locations     | CNN  |
|    22789 | misc          | CNN  |
|    65306 | organizations | CNN  |
|   132307 | people        | CNN  |
+----------+---------------+------+

How can I set up that query to provide that sort of counting?

Upvotes: 0

Views: 32

Answers (1)

wogsland
wogsland

Reputation: 9508

You'll need a join:

select count(*), entity_type, site 
from entities 
left join articles_entities on entity_id = entities.id
left join articles on article_id = articles.id
group by entity_type, site;

I used a left join on the assumption that you may have entities that don't correspond to articles that you want included in the results. If this is not the case then you can remove the lefts.

Upvotes: 1

Related Questions