Count the number of times a string occours in a row with the type SET

Question

My database has got four columns: one, two, three and four each one has the type SET('a','b','c','d').

I want to know how many times a, b, c and d occurs in each column.

I guess I could do something like this

SELECT
(SELECT COUNT(*) FROM database as d WHERE d.a = 'one') AS one,
(SELECT COUNT(*) FROM database as d WHERE d.a = 'two') AS two,
(SELECT COUNT(*) FROM database as d WHERE d.a = 'three') AS three,
(SELECT COUNT(*) FROM database as d WHERE d.a = 'four') AS four
FROM database
LIMIT 1

four times which I know would work. Or I could fetch the entire result and count what's in the array.

Can I somehow do this more efficient?

Thanks

Answer 1

You can combine multiple counts in a single select using case and sum:

SELECT 
    sum(case when d.a = 'one' then 1 else 0 end) as SumAIsOne
,   sum(case when d.b = 'two' then 1 else 0 end) as SumBIsTwo
,   <other counts here>
FROM database

Answer 2

select one,count(*) from db group by one
select two,count(*) from db group by two
etc