我有以下表列:ID (ID), Type (string)。可以是:open或close)
我想获得一个输出计数,其中对于每个唯一ID,我计算ID获得打开类型的次数和获得关闭类型的次数。
所以输出看起来像这样:
ID | openCount | closeCount
10 | 23 | 2
任何想法?
select ID,count(*) AS "openCount" where type =" open" group by ID union select ID,count(*) AS "closeCount" where type =" close" group by ID;
应该可以。它的count(*)堆栈溢出似乎不像那样:)
SELECT id, SUM(type='open') openCount, SUM(type='closed') closedCount FROM table GROUP BY id;
发生的是SUM()函数聚合所有type='open'
和type='closed'
值的所有行共享相同的id
值,如果为真则为1
,如果为假则为0
。