By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table cats(name varchar(10), owner varchar(10), no_of_kittens int)
insert into cats values('bob', 'hanna', 2)
insert into cats values('tip', 'frank', null)
insert into cats values('spark', 'george', 6)
insert into cats values('lucky', 'rita', null)
insert into cats values('lady', 'rita', 3)
CREATE TABLE cats_with_kittens (
name VARCHAR(255) NOT NULL,
no_of_kittens varchar(20) default ''
);
INSERT INTO cats_with_kittens
SELECT name, case when count(no_of_kittens) = 0 then ''
else count(no_of_kittens)
end
FROM cats
GROUP BY name;
select * from cats_with_kittens
| name | no_of_kittens |
|---|---|
| bob | 1 |
| tip | |
| spark | 1 |
| lucky | |
| lady | 1 |