Search code examples
sqljoinprestotrino

Column populated with table string occurrences joined to original SQL table

I would like to query for a table that shows me my raw data as well as a column that counts the occurrences of a value ('ID') subject to a conditions ('Type' NOT LIKE '%jack%')

ID big med Type
1001 x 1 lumber_jack
1002 y 2 jack_knife
1001 z 3 peter_pan
1005 a 4 rock_star
1005 b 5 paper_hands
1007 c 6 to_the_moon

Example: 2 occurrences of ID = 1001 but only one that does not contain '%jack%' in 'Type' column therefore count = 1

My desired output is:

ID big med Type count
1001 x 1 lumber_jack 1
1002 y 2 jack_knife 0
1001 z 3 peter_pan 1
1005 a 4 rock_star 2
1005 b 5 paper_hands 2
1007 c 6 to_the_moon 1
Solution

  • you can do it using window function:

    select * , sum(case when Type NOT LIKE '%jack%' then 1 end) over (partition by ID)  as count 
from table