CODEWITHSUNDEEP
mysqlsqljoin
doug22
doug22

Reputation: 77

SQL join two tables without common column using count

In mysql I am wanting to count the number of followers each user has and the table doesn't have related columns. I would like the name and the total followers to display, but each person is displaying as having 1 follower which isn't the expected outcome.

Insert Scripts

insert into user (email, password, first_name, last_name) values ('[email protected]', 'E7WC5qpzFPA1', 'Richmound', 'Clunan');
insert into user (email, password, first_name, last_name) values ('[email protected]', 'MjEUC4', 'Herculie', 'Turn');
insert into user (email, password, first_name, last_name) values ('[email protected]', 'r7BYgEI0', 'Wenonah', 'Rogier');
insert into user (email, password, first_name, last_name) values ('[email protected]', 'FMiUgn66amxW', 'Aleta', 'Avramovsky');
insert into user (email, password, first_name, last_name) values ('[email protected]', '2UzOXfYyK', 'Teddi', 'Doldon');
insert into user (email, password, first_name, last_name) values ('[email protected]', '7GVGQDIBt8fa', 'Hinda', 'Addyman');
insert into user (email, password, first_name, last_name) values ('[email protected]', 'KqklbW', 'Randi', 'Golsby');
insert into user (email, password, first_name, last_name) values ('[email protected]', 'dwUaaMlu', 'Mason', 'Hulson');
insert into user (email, password, first_name, last_name) values ('[email protected]', 'qDegvwr8A1n', 'Kareem', 'Dorkins');
insert into user (email, password, first_name, last_name) values ('[email protected]', 'WucCOGk8', 'Jillane', 'Penketh');

INSERT INTO `following` ( following_id, follower_id)
VALUES (1, 2), (2, 1), (1, 3), (3, 1), (1, 4), (4, 1), (1, 5), (5, 1), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10);

Following

 - id
 - following_id
 - followers_id

User

- id
 - first_name
 - last_name
 - username

the query that I have written

select first_name, count(follower_id)
from `following`
join `user`
on `user`.id = `following`.id
group by first_name
order by count(follower_id) desc;

Expected output:

user|followers
Richmound | 9
Herculie  | 1
Wenonah   | 1
 Aleta    | 1 
Teddi     | 1
Hinda     | 0
Randi     | 0
Mason     | 0
Kareem    | 0
Jillane   | 0

Upvotes: 2

Views: 377

Answers (2)

user16417879
user16417879

Reputation:

SQL is utilized to relate tables utilizing keys, finding where they are equal, not equal, or a mixture, and lastly union or union all. If the tables have no characteristic that relates the two, then I would assume you'd want to build a running sum of 1 on both tables, then join that value. I can't offer syntax or a solution due to not understanding the database. Looks like events and not relational database tables, i'd question data architecture and the application writing the data before writing the SQL.

Crazy sql is not the answer, im sorry i suggested it above.

Upvotes: 2

Amit Verma
Amit Verma

Reputation: 2490

it's simple. try below query. I am assuming that you need user result also when there is no follower.

SELECT u.first_name,  count(f.following_id)followers
FROM [user] u
Left Outer Join following f ON (u.id = f.following_id)
Group By u.first_name
ORDER BY count(f.following_id) DESC

If you don't need users with ZERO followers then user inner join.

    SELECT u.first_name,  count(f.following_id)followers
    FROM [user] u
    inner Join following f ON (u.id = f.following_id)
    Group By u.first_name
    ORDER BY count(f.following_id) DESC

Upvotes: 2

Related Questions