MySQL query to count non-null values in a single row

Question

I'm trying to put together a MYSQL query that will count the number of Non-Null (or better yet, non-zero) values in select fields in a single row and then sort from lowest to highest (based on the count). For example, I have a table with 5 fields... ID, Name, Score_1, Score_2, Score_3. I want to count how many times the value "0" exists in Score_1, Score_2 and Score_3 for each record, then sort from most non zero values to least.

ID   Name   Score_1   Score_2   Score_3
1    Dan    8         7         0
2    Joe    0         0         3
3    Chris  0         0         0
4    Mike   4         5         5

I assume the query has to look something like this...

Select ID, Name, Score_1, Score_2, Score_3 where (???) ORDER BY (???)

Output should look like this (ID 4 is displayed first since it has the least amount of non-zero entries)...

ID   Name   Score_1   Score_2   Score_3
4    Mike   4         5         5
1    Dan    8         7         0
2    Joe    0         0         3
3    Chris  0         0         0

I'm somewhat new to mysql query's, so any help would be greatly appreciated. I thought the COUNT function would help, but that function appears to count columns from all rows. Perhaps there is a way to use the COUNT function and limit it to a singel row so it can be sorted by that row count?

Mark Byers · Accepted Answer · 2010-08-31 19:09:38Z

2

This should do what you want:

SELECT ID, Name, Score_1, Score_2, Score_3
FROM Table1
ORDER BY (Score_1 = 0) + (Score_2 = 0) + (Score_3 = 0)

Result:

ID  Name   Score_1  Score_2  Score_3
4   Mike   4        5        5      
1   Dan    8        7        0      
2   Joe    0        0        3      
3   Chris  0        0        0

answered Aug 31, 2010 at 19:09

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Over a year ago

Awesome! This works... exactly what i was looking for. Thought for sure it would have to be more complicated.

Charles Bretana · Accepted Answer · 2010-08-31 22:39:58Z

0

try This:

Select id, Count1, Count2, Count3, Count4
From
    (Select 
        Sum(Case When IsNull(Score_1,0) = 0 Then 1 Else 0 End) Count1,
        Sum(Case When IsNull(Score_2,0) = 0 Then 1 Else 0 End) Count2,
        Sum(Case When IsNull(Score_3,0) = 0 Then 1 Else 0 End) Count3,
        Sum(Case When IsNull(Score_4,0) = 0 Then 1 Else 0 End) Count4
    From Table
    Group By Id) Z  -- This column (Id) better not be the PK for this table!!!
Order By Count1 + Count2 + Count3 + Count4

edited Aug 31, 2010 at 22:39

answered Aug 31, 2010 at 19:09

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

1 Comment

Dan Over a year ago

I wasn't able to get this to work... it looks like it might be missing a closed paren after 'Sum' but after adding closed parenthesis, I still can't get it to work.

Collectives™ on Stack Overflow

MySQL query to count non-null values in a single row

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related