Count number of Not Null fields within a Row

Question

Possible Duplicate:
Count the Null columns in a row in SQL

I've had a dig around for answers to this, but can't find either a working or suitable answer and I'm a novice with SQL.

I've got a table containing teams for an event, and amongst other columns I have team_member_1, team_member_2, team_member_3, team_member_4, team_member_5. Each one stores the name of that team memember, each team has a different amount of members (between 3 and 5).

I'm trying count the number of people in a team by counting how many columns/ fields within that row/team are NOT NULL.

If my head if would go something like:

Select Count NOT NULL team_member_1, team_member_2, team_member_3,  
                      team_member_4, team_member_5 
from Teams 
where team_id = 5

Of course this doesn't work. I then want to times this figure by £20 (as the event cost is £20 per head), to give me the amount each team owes, and that will be echo'd out on the website. Hope all makes sense.

Your `Teams` table is not well normalized - you need to restructure it to have a `teamId`, `memberId` and possibly a `memberPosition` if that's important. This will make queries much easier. — Oded, Dec 31 '11 at 17:15
(Yes I know that possible duplicate question asks the opposite but essentially the same...) — Martin Smith, Dec 31 '11 at 17:19

score 4 · Answer 1 · answered Jan 01 '12 at 13:07

If your server product supports the boolean data type and implicitly converts it to integer when in a proper context, like MySQL does, for instance, then you could try the following pattern:

SELECT
  (team_member_1 IS NOT NULL) +
  (team_member_2 IS NOT NULL) +
  (team_member_3 IS NOT NULL) +
  (team_member_4 IS NOT NULL) +
  (team_member_5 IS NOT NULL) AS MemberCount
FROM Teams
WHERE team_id = 5

gdoron · Answer 2 · 2011-12-31T17:28:54.280

2

You should have a relation table TeamMember -> Team with relation of 1:n \ n:n.

Then in your query join the two tables,Group by the team and count the team members.

select t.Id, count(tm.Id)
from teams t, teams_members tm
where t.Id = tm.Team_Id
group by t.Id

edited Dec 31 '11 at 17:28

answered Dec 31 '11 at 17:16

gdoron

147,333
58
291
367

score 1 · Answer 3 · answered Dec 31 '11 at 17:17

Imho, that's a really bad design :)

First of all, I would normalize the db. If that is done, your question about the number of team members is a simple SELECT COUNT(*) ...

Make a table for the teams, one for the member and a third where you "concat" the team member with a team.

Damian Leszczyński - Vash · Answer 4 · 2012-01-01T13:51:18.380

1

The key to solve your problem is to use specific if function

For Oracle it could be DECODE.

SELECT 
 DECODE(COL1, null, 0, 1) +
 DECODE(COL2, null, 0, 1) +
 DECODE(COL3, null, 0, 1) +
 DECODE(COL4, null, 0, 1) AS Result
FROM YOUR_TABLE

For MySQL it could be IF.

SELECT 
 IF(COL1 is null, 0, 1) +
 IF(COL2 is null, 0, 1) +
 IF(COL3 is null, 0, 1) +
 IF(COL4 is null, 0, 1) AS Result
FROM YOUR_TABLE

I you are using the SQL Server, you should use CASE statement

SELECT
    CASE COL1 WHEN IS NULL THEN 0 ELSE 1 END + 
    CASE COL2 WHEN IS NULL THEN 0 ELSE 1 END +
    CASE COL3 WHEN IS NULL THEN 0 ELSE 1 END 
FROM YOUR_TABLE

edited Jan 01 '12 at 13:51

answered Dec 31 '11 at 17:21

Damian Leszczyński - Vash

30,365
9
60
95

2

You could use case for all 3! – Ben Dec 31 '11 at 17:32
+1 for the 3rd version that should work in almost all DBMS. – ypercubeᵀᴹ Dec 31 '11 at 18:21
1

+1/-1 MySQL doesn't have a four-arg IF() operator. CASE WHEN NULL is *never* true. The DECODE example looks right, though. (Oracle will treat NULLs as equal there). – pilcrow Jan 01 '12 at 03:46
1

Yes, the Case should be: `CASE WHEN Col1 IS NULL THEN ...` – ypercubeᵀᴹ Jan 01 '12 at 04:08
And `IF()` should be something like this: `IF(Col1 IS NULL, 0, 1)`. – Andriy M Jan 01 '12 at 13:10
All errors have been fixed, thank for pointing them out. – Damian Leszczyński - Vash Jan 01 '12 at 13:53
Sorry.. I'm using MySQL. Your answer makes sense, but I can't get it working. Here's what I've got (Thanks in advance): `There are ". $number_of_people . " people in the team."; ?>` – user1124503 Jan 02 '12 at 22:00
Try to run it on some RDBMS, then use in script. With PHP I can not help you. – Damian Leszczyński - Vash Jan 02 '12 at 23:59

score -1 · Answer 5 · answered Dec 31 '11 at 17:23

-1

As others have pointed out, this is a bad design and you should try to normalize. However, I'd try something like this:

Select Count(*) from teams where
 (team_id = 5) and
 (team_member_1 is not null or
  team_member_2 is not null or
  team_member_3 is not null or
  team_member_4 is not null or
  team_member_5 is not null)

You should at least normalize to the first normal form by creating a Team table and a Member table and reference Member to Team.

answered Dec 31 '11 at 17:23

Eddie Paz

2,219
16
11

1

This would count Not Null columns but would count rows where at least one field is not null. – ypercubeᵀᴹ Dec 31 '11 at 18:25

Count number of Not Null fields within a Row

5 Answers5

Linked

Related