postgresql - sql查询来分解每个组合的计数

Question

我需要一个 Postgresql 查询，它返回每种类型的记录组合的计数。

例如，我有一个表 T，其中包含 A、B、C、D、E 列和其他不重要的列:

Table T--------------A | B | C | D | E

The query should return a table R with the values from columns A, B, C, D, and a count for how many times each configuration occurs with the specified E value.

Table R---------------A | B | C | D | count

When all of the counts for each record are added together, it should equal the total number of records in the original table.

It seems like a very simple problem, but due to my lack of SQL knowledge, I cannot figure out how to do this.

The only solution I can think of is this:

select a, b, c, d, count(*)
  from T
  where e = 'abc'
  group by a, b, c, d

但是当从这个查询中添加计数时，它比原始表的计数要多得多。似乎不应该使用 count(*) ，或者我只是完全以错误的方式解决这个问题。我真的很感激任何关于我应该如何去做的建议。谢谢大家。

Answer 1

NULL 值不可能欺骗您。考虑这个演示:

WITH t(a,b,c,d) AS (
    VALUES
     (1,2,3,4)
    ,(1,2,3,NULL)
    ,(2,2,3,NULL)
    ,(2,2,3,NULL)
    ,(2,2,3,4)
    ,(2,NULL,NULL,NULL)
    ,(NULL,NULL,NULL,NULL)
    )
SELECT a, b, c, d, count(*)
FROM   t
GROUP  BY a, b, c, d
ORDER  BY a, b, c, d;

 a | b | c | d | count
---+---+---+---+-------
 1 | 2 | 3 | 4 |     1
 1 | 2 | 3 |   |     1
 2 | 2 | 3 | 4 |     1
 2 | 2 | 3 |   |     2
 2 |   |   |   |     1
   |   |   |   |     1

这里肯定还有别的误会。