CODEWITHSUNDEEP
mysql
user1753622
user1753622

Reputation: 288

count number if times a column has a certain value

I have a table with an id column (same id repeated across rows) and columns h1 to h18 where each column could contain either the word blob, par or birdie.

I want to count the number of times the word blob, par or birdie appears in columns h1 to h18 and for each id.

I want to end up with a table like ...

id blob par birdie
-- ---- --- ------
1  5    10  3
2  2    15  1
3  8    8   2

How would I do this?

Upvotes: 0

Views: 384

Answers (2)

fthiella
fthiella

Reputation: 49049

Maybe you can also consider this:

select
  id,
  sum(word='blob') as blob,
  sum(word='par') as par,
  sum(word='birdie') as birdie
from
  (select id, h1 word from words union all
   select id, h2 from words union all
   select id, h3 from words union all
   ...) w
group by id

to make the query faster, you could also write the subquery like this:

(select id, h1 word from words where h1 in ('blob', 'par', 'birdie') union all
 select id, h2 from words where h2 in ('blob', 'par', 'birdie') union all
 ...)

but it then becomes a little ugly. I would suggest you to normalize your database.

Upvotes: 0

eggyal
eggyal

Reputation: 125855

It sounds like you should probably normalise your schema somewhat, but as things stand:

SELECT id,
       (h1='blob'  )+(h2='blob'  )+...+(h18='blob'  ) AS blob,
       (h1='par'   )+(h2='par'   )+...+(h18='par'   ) AS par,
       (h1='birdie')+(h2='birdie')+...+(h18='birdie') AS birdie
FROM   my_table

Upvotes: 3

Related Questions