Executing computing multiple values in a single query in mysql-CodePudding

Coinsider two tables User and Posts with 1-N relation between user-posts. Is there a way I can execute a query in which I can return users with atleast one post, and users with more than 1 post. I want the result to be like this

Explanation:
user 1 Has 3 Posts, so increment 1 on both count(1 or more post),count (more than 1) column //category: Design
user 2 Has 2 Posts, so increment 1 on both count(1 or more post),count (more than 1) column // category: Engineering
user 3 Has 1 Post, so increment 1 on count(more than 1) column only. // category: Design

CodePudding user response：

We can use a double aggregation approach:

SELECT Category,
       SUM(cnt >= 1) AS cnt_1_or_more,
       SUM(cnt > 1) AS cnt_more_than_1
FROM
(
    SELECT u.Category, COUNT(p.user_id) AS cnt
    FROM User u
    LEFT JOIN Post p ON p.user_id = u.user_id
    GROUP BY u.Category, u.user_id
) t
GROUP BY Category;