postgresql 如何在两列上分组并在第三列上计数？

efzxgjgh 于 2023-04-20 发布在 PostgreSQL

关注(0)|答案(2)|浏览(146)

我的postgres表中有这样的数据。

我想得到这样的结果。

我尝试了这样的嵌套查询。我只做了column1在column2中有多个值的行。例如，C3，c4每个都只有一个，我想跳过它。

select column1, column2, dCount
from (select column1, column2, count(column3) as dCount
      from schema.table
      where column1 != 0
      group by column1, column2) t
group by t.column1, t.column2, t.dCount
having count(t.column2) > 0;

但我得到了0行。

postgresql

来源：https://stackoverflow.com/questions/76010546/how-to-group-by-on-two-columns-and-count-on-the-third-column

2条答案

按热度按时间

ie3xauqp1#

您可以使用group by和count()和HAVING COUNT(column3) > 1来只获取具有多个数据的记录：

SELECT column1, column2, count(column3) as dCount
FROM supportassist.tbldeviceservice
GROUP BY column1, column2
HAVING COUNT(column3) > 1

Demo here

赞(0）回复(0）举报 2023-04-20

mctunoxg2#

我们可以使用以下两步聚合查询：

WITH cte AS (
    SELECT column1, column2, COUNT(column3) AS dCount,
           MIN(column2) OVER (PARTITION BY column1) AS min_column2,
           MAX(column2) OVER (PARTITION BY column1) AS max_column2
    FROM schema.table
    WHERE column1 != 0
    GROUP BY column1, column2
)

SELECT column1, column2, dCount
FROM cte
WHERE min_column2 <> max_column2
ORDER BY column1, column2;

赞(0）回复(0）举报 2023-04-20

我来回答

postgresql 如何在两列上分组并在第三列上计数？

2条答案

相关问题

热门标签

最新问答