Hi,

I have a table that contains the following fields
StudentId , NoOfChildren, Over18 stat
 1 2 null 0
 2 1 1 -1
 3 2 0 0
 4 3 1 0
studentid is an identity field.

What I need to do is calculate the number of children per household
(NoOfChildren -over18)+stat
and put them into columns such as the following:
1-3 4-5
  1 3

If in the table there is only one family with 1-3 household members it goes under 1-3, if there are 3 families with 4-5 household members it will go under column 4-5.
also if stat=0 then i need to add by 2 else add by one.

How can I accomplish that?

Thanks

something like this....little confused what exactly you want but

SELECT (NoOfChildren + CASE Over18
                        WHEN 1 Then -1
                        ELSE 0 END +
                       CASE Stat
                WHEN 0 THEN 2
                ELSE 1 END ) As TotalChildren
From TestC

Thanks for the help :)
I have another question.
The result that I get now is as follows:
TotalChildren
8
1
8
8
8
1
1
8
1
1
1
1
1
1
Now I need to count the TotalChildren. If for example there are five rows that the TotalChildren=8 i need to place 5 in a column called Five-Eight
IF there are 9 rows of TotalChildren =1
Then I need to place 9 under a column called Nine-Eleven.

The result can be displayed as follows:
CountTotalChildren Name
 5 Five-Eight
 6 Five-Eight
 9 Nine-Eleven

Thanks