GROUP BY和COUNT在PostgreSQL中

本文介绍了GROUP BY和COUNT在PostgreSQL中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

查询：

SELECT COUNT(*) as count_all, posts.id as post_id FROM posts INNER JOIN votes ON votes.post_id = posts.id GROUP BY posts.id;

在Postgresql中返回 n p>

Returns n records in Postgresql:

count_all | post_id -----------+--------- 1 | 6 3 | 4 3 | 5 3 | 1 1 | 9 1 | 10 (6 rows)

我只想检索返回的记录数： code> 6 。

我使用子查询来实现我想要的，但这似乎不是最佳的：

I used a subquery to achieve what I want, but this doesn't seem optimum:

SELECT COUNT(*) FROM ( SELECT COUNT(*) as count_all, posts.id as post_id FROM posts INNER JOIN votes ON votes.post_id = posts.id GROUP BY posts.id ) as x;

我如何在PostgreSQL中获得此上下文中的记录数？

How would I get the number of records in this context right in PostgreSQL?

推荐答案

我想你只需要 COUNT（DISTINCT post_id）FROM votes 。

请参阅 http中的4.2.7。汇总表达式部分：//www.postgresql/docs/current/static/sql-expressions.html 。

编辑：根据Erwin的评论更正了我的粗心错误。

Corrected my careless mistake per Erwin's comment.