Hello, good, I have a problem when doing two counts at the same time from different tables in a sql database.
Briefly, this would be the database:
CREATE TABLE posts(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
title VARCHAR(50) NOT NULL
);
CREATE TABLE likes(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
post INT NOT NULL,
FOREIGN KEY fk_post(post) REFERENCES posts(id)
);
CREATE TABLE comments(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
post INT NOT NULL,
comment VARCHAR(100) NOT NULL,
FOREIGN KEY fk_comment(post) REFERENCES posts(id)
);
INSERT INTO posts(title) VALUE ('post 1'),('post 2'),('post 3');
INSERT INTO likes(post) VALUE (1),(1),(2);
INSERT INTO comments(post,comment) VALUE (1,'hola'),(1,'hola'),(3,'hola'),(3,'hola'), (2,'hola');
The goal is for the query to return the following:
title | likes | comments |
---|---|---|
post1 | two | two |
post2 | 1 | 1 |
post3 | 0 | two |
I have managed to make a count by doing the following:
SELECT
posts.title,
count(l.post) likes
FROM posts
LEFT JOIN likes l ON posts.id = l.post
GROUP BY posts.id
With this query it returns the following (and if I do it with the 'comment' table it also works):
title | likes |
---|---|
post1 | two |
post2 | 1 |
post3 | 0 |
The problem is when doing the two counts at the same time, I have tried the following:
SELECT
posts.title,
count(l.post) likes,
count(c.post) comments
FROM posts
LEFT JOIN likes l ON posts.id = l.post
LEFT JOIN comments c ON posts.id = c.post
GROUP BY posts.id
But it returns the following:
title | likes | comments |
---|---|---|
post1 | 4 | 4 |
post2 | 1 | 1 |
post3 | 0 | two |
( UPDATED )
I have found the way to do the two count, with the following query:
SELECT
p.id,
(SELECT COUNT(*) FROM likes WHERE post = p.id) as likes,
(SELECT COUNT(*) FROM comments WHERE post = p.id) as comments
FROM (SELECT DISTINCT id FROM posts) p;
The problem is that when I put the p.title in the SELECT it gives me an error