Get record where association count greater than 0

I want to get record if association count is greater than zero. let say i have a categorytype model and against each categorytype we have categories. now i want to get all categorytypes where associated category count is greater than 0. i need where condition in the following query


You can use an aggregate query to get you there. See SQL example below:

drop table cats;

create table cats
id string,
category string,
type string,
constraint “primary” primary key (id ASC)

insert into cats values
(‘1’, ‘fashion’, ‘shirt’),
(‘2’, ‘fashion’, ‘pants’),
(‘3’, ‘fashion’, ‘belt’),
(‘4’, ‘auto’, ‘belt’),
(‘5’, ‘auto’, ‘brake pad’),
(‘6’, NULL, ‘widget’)

SELECT type, count(distinct category)
FROM cats
HAVING count(distinct category) > 0