Count the occurences of a value over a certain amount from multiple columns

[James Hummerston]

HI,

First post and total newbie to SQL so please forgive my ignorance! In Excel i used COUNTIFS and selected each column and the criteria.

I have a dataset of 5000 records with multiple columns that I want to count if the value is above 50.

So columns G1, G2 ..... to G12 have values ranging from NULL to 100, and my ultimate aim is to add a calculated field to my table "data" called "Over50" that shows the count of the values over 50 from the 12 columns.

I have tried to workout the code myself and this is what i have ended up with, but it doesn't work. I want each record to have the value for that row to be the count of entries over 50, this could range from 0 to 12, usually 0 to 3 or 4.


SELECT *,
SUM(CASE
WHEN g1 > 50 THEN 1
WHEN g2 > 50 THEN 1
WHEN g3 > 50 THEN 1
WHEN g4 > 50 THEN 1
WHEN g5 > 50 THEN 1
WHEN g6 > 50 THEN 1
WHEN g7 > 50 THEN 1
WHEN g8 > 50 THEN 1
WHEN g9 > 50 THEN 1
WHEN g10 > 50 THEN 1
WHEN g11 > 50 THEN 1
WHEN g12 > 50 THEN 1
END) AS over50
FROM data
GROUP BY data_id


data_id is the unique key, but this code gives me the column i want with 1 or NULL as the result which is correct in that if any of the columns has a value over 50 it has the result 1, but some columns should show 2, 3,4 or 5 as i want the count.

Once i work this out hopefully i can try to use "GENERATED ALWAYS AS" to update the table to store the "Over50" column as this variable will be used all the time once created.

Many thanks