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

Question

I am trying to run a SQL query on a result table like 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 to group all the similar rows and count the col1 and col2 as just 1 instead of taking a sum, which should look something like this:

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

I tried some sql queries but it doesn't work. I tried using common table expressions ( like 'with' keyword) also but is there a better way of writing the SQL?

Wouldn't `count_col1` be 2 for `temp.%` and `perm.%`? if it counted the underlying rows? — StuartLC, Dec 16 '14 at 09:20
What have you tried so far? What does your base data look like? How do you know when to stop the match for `property`? Say, what happens if you have a `'tent'` value, or `'temporary'`, or something? — Clockwork-Muse, Dec 16 '14 at 09:24
@StuartLC Yes, count_col1 should be 2. But consider this example if it helps to understand the problem: I have an address, eg. 34, gold street,brooklyn, NY, 11201. This address has many parts, like the state, zip, street address1, street address2 which is stored as different fields in the database. If any of these parts is updated, then I want to show Address update as 1(as a whole instead of showing 3 different updates). I hope my example was clear ? — Neha M, Dec 16 '14 at 09:34
As always, table definitions (`\d tbl` in psql) and your version of Postgres would be helpful. In particular, can columns be NULL? What are your exact data types? — Erwin Brandstetter, Dec 16 '14 at 09:53

score 1 · Accepted Answer · edited May 23 '17 at 11:57

SELECT name, split_part(property, '.', 1) AS propertyname
     , bool_or(col1) AS col1
     , bool_or(col2) AS col2
FROM   tbl
GROUP  BY 1, 2;

Assuming col1 and col2 are boolean (which would seem best here). If you are using integer:

CASE WHEN sum(col1) > 0 THEN 1 ELSE 0 END AS col1

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

Split comma separated column data into additional columns

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

1 Answers1