CODEWITHSUNDEEP
mysqlsql
Mike Timmons
Mike Timmons

Reputation: 1

SQL - multiple counts for number matching rows in a table

I have a table that looks like this:

+-----------------------------+
| ID | item1 | item2 | item3  |
|  1 |    2  |   5   |   4    |
|  2 |    5  |   5   |   2    |
|  3 |    3  |   4   |   5    |
+-----------------------------+

ID is a unique integer and the values for the 3 item columns are integers that range from 1-5 (and may be NULL). I'd like a SQL statement that returns, for each item column, the number of rows that have a value greater than or equal to 4. So, the desired output of this query would be:

+-----------------------+
| item1 | item2 | item3 |
|    1  |   3   |   2   |
+-----------------------+

Is there a single query that would achieve this?

Upvotes: 0

Views: 37

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

Try something like this 

Select
    Sum(item1>4) as item1, 
    Sum(item2>4) as item2, 
    Sum(item3>4) as item3 
From Yourtable

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a sum on case when 

 select 
   sum( case when item1 > 4 then 1 else 0 end) as item1
  ,sum( case when item2 > 4 then 1 else 0 end) as item2
  ,sum( case when item3 > 4 then 1 else 0 end) as item3
from my_table

Upvotes: 2

Related Questions