Count number of Not Null fields within a Row [dupl

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.

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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