Count each column if the value is met for a row-CodePudding

I've been searching far and wide (pun intended) but haven't found a solution yet.

So I got a table with 19 columns.

pokemon name	against_bug	against_dark	against_dragon	against_electric	against_fairy	against_fight	against_fire	against_flying	against_ghost	against_grass	against_ground	against_ice	against_normal	against_poison	against_psychic	against_rock	against_steel	against_water

Each of the "against_xxx" columns can have a value of 0,5, 1, 2 or 4.

I want each row to count how many of these columns have a value of 2 or higher in order to determine which pokemon has the most vulnerabilities.

I have no idea how to approach this.

CodePudding user response：

Please look at Stu's suggestion in the comments on your question. Normalizing your table would be a great help.

Now you need to do something like this:

SELECT 
   pokemon, 
   CASE WHEN against_bug >= 2 THEN 1 ELSE 0 END   
   CASE WHEN against_dark >= 2 THEN 1 ELSE 0 END  
   CASE WHEN against_dragon >= 2 THEN 1 ELSE 0 END  
   CASE WHEN against_electric >= 2 THEN 1 ELSE 0 END   
   --[....repeat this for all your columns....]
FROM your_table

A normalized table would look like this:

pokemon	against_type	against_value
Pickachu	against_bug	1
Pickachu	against_dark	2
Pickachu	against_dragon	0.5
Pickachu	against_electric	1
Pickachu	(etc)	(etc)
Blastoid	against_bug	1
Blastoid	against_dark	2
Blastoid	against_dragon	2
Blastoid	against_electric	4
Blastoid	(etc)	(etc)

In this case you could write a much simpler query:

SELECT
   pokemon, 
   count(*) AS number_of_vulnerabilities
FROM your_table
WHERE against_value >= 2
GROUP BY pokemon