按唯一计数进行不同计数和求和

发布于 2024-12-22 09:46:15 字数 456 浏览 1 评论 0原文

我有一个包含教程的表（tutorial_id、user_id、tutorial_date），我的任务是找出有多少用户完成了特定数量的教程。

因此，如果有 5 个用户完成了 6 个教程，3 个用户完成了 2 个教程，我想要以下输出：

Tutorial_Count (unique)            Number of Students With this Tutorial Count
6                                  5
2                                  3

我可以获得不同的计数（第一列）：

SELECT DISTINCT COUNT(user_id) from tutorials group by user_id

但我不知道如何计算数量符合这些计数括号的用户数。

原文

I've got a table with tutorials (tutorial_id,user_id,tutorial_date) and I'm tasked with finding out how many users did a particular number of tutorials.

So, if there were 5 users who did 6 tutorials, and 3 users who did 2 tutorials, I'd want the following output:

Tutorial_Count (unique)            Number of Students With this Tutorial Count
6                                  5
2                                  3

I can get the distinct counts (first column):

SELECT DISTINCT COUNT(user_id) from tutorials group by user_id

But I don't know how to count the number of users who fit into those count brackets.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

鱼忆七猫命九 2024-12-29 09:46:15

未经测试，但试试这个：

;WITH cte AS
(
    SELECT COUNT(user_id) AS Tutorial_Count
    FROM tutorials
    GROUP BY user_id
)
SELECT DISTINCT
    Tutorial_Count,
    COUNT(*) OVER (PARTITION BY Tutorial_Count) AS [Number of Students With this Tutorial Count]
FROM
    cte;

Untested, but try this:

;WITH cte AS
(
    SELECT COUNT(user_id) AS Tutorial_Count
    FROM tutorials
    GROUP BY user_id
)
SELECT DISTINCT
    Tutorial_Count,
    COUNT(*) OVER (PARTITION BY Tutorial_Count) AS [Number of Students With this Tutorial Count]
FROM
    cte;

回复收藏 0 原文

~没有更多了~