user2340312 user2340312 - 2 months ago 8
SQL Question

how to get count of columns whose value greater then 40 from table

i need to get column count whose value greater than 40

example :

id col_1 col_2 col_3

1 20 60 80


the output like this count=2

query:

select count(columns) from table where id =1 and col_values >40;


how to write query to get this please help me to write query.

Answer

Premise:

Your database is not normlized! Please, normalize it.

Solution:

Try this:

SELECT SUM(
    CASE
        WHEN col1 >= 40 THEN 1 ELSE 0
    END +
    CASE
        WHEN col2 >= 40 THEN 1 ELSE 0
    END +
    CASE
        WHEN col3 >= 40 THEN 1 ELSE 0
    END)
FROM yourtable

With this query you summarize all columns and you'll get only sum trasversal by rows.

So, if you have this result set

id col_1 col_2 col_3

1   20     60  80
2   44     22  20

You'll get 3 (two from row with id 1 and 1 for row with id 2)