Postgresql一个表的多个计数 [英] Postgresql Multiple counts for one table

问题描述

从表格中的两列，我想获得这些列中的值的统一计数。
例如，两列是：

From two columns in my table I want to get a unified count for the values in these columns. As an example, two columns are:

表：reports

|   type        |   place   |  
 ----------------------------------------- 
|   one         |   home    |  
|   two         |   school  |  
|   three       |   work    |  
|   four        |   cafe    |  
|   five        |   friends |  
|   six         |   mall    |  
|   one         |   work    |  
|   one         |   work    |  
|   three       |   work    |  
|   two         |   cafe    |  
|   five        |   cafe    |  
|   one         |   home    |  

如果我这样做：
SELECT type，count（*）from reports
按类型分组

If I do: SELECT type, count(*) from reports group by type

我获得：

|   type        |   count   |  
-----------------------------  
|   one         |   4       |  
|   two         |   2       |  
|   three       |   2       |  
|   four        |   1       |  
|   five        |   2       |  
|   six         |   1       | 

我试图得到这样的东西:(一个最右边的列，我的类型分组在一起，每个地方的计数值）
我得到：

Im trying to get something like this: (one rightmost column with my types grouped together and multiple columns with the count vales for each place) I get:

|   type        |   home    |   school  |   work    |   cafe    |   friends |   mall    |  
-----------------------------------------------------------------------------------------  
|   one         |   2       |           |   2       |           |           |           |  
|   two         |           |   1       |           |   1       |           |           |  
|   three       |           |           |   2       |           |           |           |  
|   four        |           |           |           |   1       |           |           |  
|   five        |           |           |           |   1       |   1       |           |  
|   six         |           |           |           |           |           |   1       |  

这将是每个地方像上面这样运行计数的结果： p>

which would be the result of running a count like the one above for every place like this:

SELECT type, count(*) from reports where place  = 'home'
group by type
SELECT type, count(*) from reports where place  = 'school'
group by type
SELECT type, count(*) from reports where place  = 'work'
group by type
SELECT type, count(*) from reports where place  = 'cafe'
group by type
SELECT type, count(*) from reports where place  = 'friends'
group by type
SELECT type, count(*) from reports where place  = 'mall'
group by type

这是否可能与postgresql？

Is this possible with postgresql?

提前感谢。

推荐答案

您可以在这种情况下使用 case -

you can use case in this case -

SELECT type, 
       sum(case when place  = 'home' then 1 else 0 end) as Home,
       sum(case when  place  = 'school' then 1 else 0 end) as school,
       sum(case when  place  = 'work' then 1 else 0 end) as work,
       sum(case when  place  = 'cafe' then 1 else 0 end) as cafe,
       sum(case when  place  = 'friends' then 1 else 0 end) as friends,
       sum(case when  place  = 'mall' then 1 else 0 end) as mall
  from reports
 group by type

它应该可以解决你的问题

It should solve your problem

这篇关于Postgresql一个表的多个计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！