Postgresql一个表的多个计数

从我的表中的两列,我想得到这些列中的值的统一计数.
例如,两列是：

表：报告

|   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类型,count(*)从报表
按类型分组

我得到：

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

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

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

这将是像这样运行像上面一样的计数的结果：

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？

提前致谢.

你可以在这种情况下使用案例 –

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

应该解决你的问题

Postgresql一个表的多个计数

猜你在找的Postgre SQL相关文章