我想通过(客户端,prop1,prop2)对每组唯一行的状态求和。像:DISTINCT(client, prop1, prop2)
这是我的桌子:
client | prop1 | prop2 | status
----------------------------------
name1 | pr100 | pr310 | OK
name1 | pr100 | pr310 | error
name1 | pr100 | pr310 | OK
name1 | pr100 | pr310 | OK
name2 | pr255 | pr320 | OK
name2 | pr255 | pr320 | OK
name2 | pr255 | pr320 | error
name2 | pr400 | pr320 | error
name3 | pr400 | pr500 | OK
name3 | pr400 | pr500 | OK
我试图得到这个:
client | prop1 | prop2 | OK | error
----------------------------------
name1 | pr100 | pr310 | 3 | 1
name2 | pr255 | pr320 | 2 | 1
name2 | pr400 | pr320 | 0 | 1
name3 | pr400 | pr500 | 2 | 0
请帮忙
这是一个聚合,而不是一个select distinct
:
select client, prop1, prop2,
sum(status = 'OK') as ok, sum(status = 'error') as error
from t
group by client, prop1, prop2;