sql - SQL Count first occurrence

Question

My original database looks like that:

TYPE CONTRACT_ID
a    101011 
c    101012
b    101011
b    101012
a    101011-1
c    101012

I am trying to get data, grouped by TYPE, counting unique CONTRACT_ID, but some contracts have subcontracts, like 101011 has subcontract 101011-1. All of them have to be counted as one contract.

I have tried distinct and it works but only partially because those subcontracts still being counted as unique entrees.

SELECT TYPE, count(distinct CONTRACT_ID) as countocc
FROM db_address
group by TYPE

I expect output like that:

TYPE  countocc
a     1 
b     2
c     1

标签： sqldistinct

Answer 1

完全忽略分包合同怎么样？当您拥有潜艇时，您似乎拥有父合同：

SELECT TYPE, count(distinct CONTRACT_ID) as countocc
FROM db_address
WHERE CONTRACT_ID NOT LIKE '%-%'
GROUP BY TYPE;