postgresql - Grouping similar rows and counting other columns based on it in SQL -

i trying run sql query on result table this:

  name   |  property   |   col1   |   col2   ___________________________________________    abc     |  temp.a     |   1      |   0  abc     |  temp.b     |   1      |   0  abc     |  perm.a     |   1      |   1  abc     |  date       |   0      |   0  abc     |  perm.b     |   1      |   0 

i want group similar rows , count col1 , col2 1 instead of taking sum, should this:

name   |   propertyname   |   count_col1   |  count_col2 ___________________________________________________________ abc    |   temp.%         |   1            |  0 abc    |   perm.%         |   1            |  1 abc    |   date           |   0            |  0 

i tried sql queries doesn't work. tried using common table expressions ( 'with' keyword) there better way of writing sql?

select name, split_part(property, '.', 1) propertyname      , bool_or(col1) col1      , bool_or(col2) col2   tbl group  1, 2; 

assuming col1 , col2 boolean (which seem best here). if using integer:

case when sum(col1) > 0 1 else 0 end col1 

split_part() takes part of string before first dot, achieving same example. example:

• split comma separated column data additional columns