MySQL Forums
Forum List  »  Newbie

Count the occurences of a value over a certain amount from multiple columns
Posted by: James Hummerston
Date: August 30, 2018 03:47PM

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

Options: ReplyQuote


Subject
Written By
Posted
Count the occurences of a value over a certain amount from multiple columns
August 30, 2018 03:47PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.