从我的表中的两列,我想得到这些列中的值的统一计数.
例如,两列是：
例如,两列是：
表：报告
| 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
应该解决你的问题
