如何在 Teradata 中再次查找计数和该计数的百分比

发布于 2025-01-19 03:09:43 字数 933 浏览 2 评论 0原文

Name   Date          Key       sce
Apple 1/1/2022    1111        1
Apple 1/1/2022    123        -11
Apple 1/1/2022    3435       -11
Mango 1/1/2022   14124       1
Mango 1/1/2022   1314       -11
Mango 1/2/2022   424          1
Mango 1/2/2022    5136      -11
Mango 1/2/2022   156          1
Mango 1/2/2022   1111      -11

我正在寻找如下所示的输出，即每个名称和每个名称日期、所有 -11 的计数以及 -11 占总数的百分比

ex：对于 2022 年 1 月 1 日，苹果在 sce 列中有两个 -11，因此计数应为 2，百分比应为 (2/3) *100..你能帮忙吗

Name    date                        count (of -11's)        percentage ( of all -11's over total)
Apple   1/1/2022                     2                                    66.7
Mango   1/1/2022                     1                                    50.0
Mango   1/2/2022                     2                                    50.0

原文

enter image description here

Name   Date          Key       sce
Apple 1/1/2022    1111        1
Apple 1/1/2022    123        -11
Apple 1/1/2022    3435       -11
Mango 1/1/2022   14124       1
Mango 1/1/2022   1314       -11
Mango 1/2/2022   424          1
Mango 1/2/2022    5136      -11
Mango 1/2/2022   156          1
Mango 1/2/2022   1111      -11

i'm looking for an output like below i.e. for each name and for each date, count of all -11's and percentage of -11 over the total

ex: for 1/1/2022, apple has two -11 in column sce so the count should be 2 and percentage should to (2/3) *100.. Can you please help

Name    date                        count (of -11's)        percentage ( of all -11's over total)
Apple   1/1/2022                     2                                    66.7
Mango   1/1/2022                     1                                    50.0
Mango   1/2/2022                     2                                    50.0

分享到QQ

分享到微博

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

发布评论

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

自我难过 2025-01-26 03:09:43

您可以使用案例表达式来限制条件计数的行：

SELECT name,
    date,
    count(CASE WHEN sce = -11 THEN sce END) as "count",
    100.0 * count(CASE WHEN sce = -11 THEN sce END)/count(*) as "percentage"
FROM yourtable
GROUP BY name, date

You can use a case expression to limit what rows are counted by a condition:

SELECT name,
    date,
    count(CASE WHEN sce = -11 THEN sce END) as "count",
    100.0 * count(CASE WHEN sce = -11 THEN sce END)/count(*) as "percentage"
FROM yourtable
GROUP BY name, date

回复收藏 0 原文

~没有更多了~