I switched from NOSQL to SQL and i can't find how to select the max of count(*)
i created users table, posts and comments. i want to select TOP 10 users with the max of posts and max of comments
SELECT
fullname,
(SELECT COUNT(*)
FROM posts WHERE posts.author_id = users.id)
AS total_posts,
(SELECT COUNT(*)
FROM comments WHERE comments.author_id = users.id)
AS total_comments
FROM users
CodePudding user response:
You can combine ORDER BY
with LIMIT
. For example:
select *
from (
-- your query here
) x
order by total_posts desc
limit 10
CodePudding user response:
First, using SQL database require you to define the JOIN
statement between the associated tables to get the referenced relationship data.
Since you require to get TOP 10
user with most posts, using traditional approach with LIMIT 10
will have problem when users have the same post count and should be considered the same rank. E.g: 4 people have 5 posts, 3 people have 6 posts..
To solve the same post count problem above, we will choose RANK query, this will treat people with the same post count as the same rank.
SELECT users.fullname, posting_rank_stats.total_posts, posting_rank_stats.posting_rank
FROM users
INNER JOIN
(
SELECT author_id, total_posts, RANK() OVER (ORDER BY post_author_count.total_posts DESC) AS posting_rank
FROM
(
SELECT COUNT(*) AS total_posts, author_id
FROM posts
GROUP BY author_id
) post_author_count
) posting_rank_stats
ON users.id = posting_rank_stats.author_id
-- we only want to search for top 10
WHERE posting_rank_stats.posting_rank <= 10;
Breaking to small pieces:
1.Get post count for each user
First, we get the post count for each user, this require the COUNT
function, and GROUP BY author_id
SELECT COUNT(*) AS total_posts, author_id
FROM posts
GROUP BY author_id
2. Calculating the rank of post count
Use the RANK()
function to calculate the rank of the post. Ranking positition is determined by the highest number of total_posts
SELECT author_id, total_posts, RANK() OVER (ORDER BY post_author_count.total_posts DESC) AS posting_rank
FROM
(
SELECT COUNT(*) AS total_posts, author_id
FROM posts
GROUP BY author_id
) post_author_count
3. Wrap up
The last step is joining the users
table with the posting_rank_stats
table to get the result. Since we only want to get in the TOP 10 user with most post, so you will need to add the condition
posting_rank_stats.posting_rank <= 10
in the WHERE
clause
Apply the same concept to get comment rank also.
Here is the db fiddle I created, combine both posting rank and commenting rank
SELECT users.id , users.fullname, posting_rank_stats.posting_rank , posting_rank_stats.total_posts, commenting_rank_stats.comment_rank, commenting_rank_stats.total_comments
FROM users
LEFT JOIN
(
SELECT author_id, total_posts, RANK() OVER (ORDER BY post_author_count.total_posts DESC) AS posting_rank
FROM
(
SELECT COUNT(*) AS total_posts, author_id
FROM posts
GROUP BY author_id
) post_author_count
) posting_rank_stats ON users.id = posting_rank_stats.author_id
LEFT JOIN
(
SELECT author_id, total_comments, RANK() OVER (ORDER BY comment_author_count.total_comments DESC) AS comment_rank
FROM
(
SELECT COUNT(*) AS total_comments, author_id
FROM comments
GROUP BY author_id
) comment_author_count
) commenting_rank_stats ON users.id = commenting_rank_stats.author_id;
id | fullname | posting_rank | total_posts | comment_rank | total_comments |
---|---|---|---|---|---|
1 | Jackson | 1 | 3 | 5 | 1 |
2 | Marry | 4 | 1 | 1 | 5 |
3 | Josh | 4 | 1 | 2 | 3 |
4 | Harley | 1 | 3 | 4 | 2 |
5 | Gordon | 4 | 1 | 5 | 1 |
6 | Barney | 4 | 1 | 2 | 3 |
7 | Gman | 4 | 1 | ||
8 | Stephan | 3 | 2 | ||
9 | Lucy | ||||
10 | Jordan | ||||
11 | Bill | ||||
12 | Rosh | ||||
13 | Lee |
CodePudding user response:
select * from (
select u.fullname,count(p.posts) as totalposts, count(c.comments) as totalcomments
from users u join posts p
on u.id=p.author_id
join comment c on u.id=c.comments
group by 1
ORDER BY count(p.posts),count(c.comments) desc ) k
limit 10;