Sum data on a column where id is some number

Question

I am new to Crystal Reports and have been trying to figure this out for a while. I am running Crystal Reports XI. Please Help.

The data:

 john   respMonitor    5
 abe    case mgmt      4   
 Cy     test1          4
 Claire respMonitor  5
 Moe    test3         2 
 Bob    case mgmt     8
 Lynn   respMonitor   4 
 Rick   test2         33
 Ray    test1         31
 Al     test1         24
 etc. 

I want to create the following data:

 group           cost
 -------------   --------
 sumOfTests1_3      124.0  <====== want a sum of tests only
 respMonitor     5.0
 caseMgmt        7.0
 respMonitor     3
 respMonitor     2
 caseMgmt        4
 etc.

So I want to be able to only sum up the tests while leaving the other groups alone.

I created a sql expression field:

 {%testCM}
(
SELECT sum("Patient"."cost") From PatientTbl 
    Where "PatientTbl"."group"= 'test1' OR
    "PatientTbl"."group"= 'test2' OR
    "PatientTbl"."group"= 'test3' 
)

In my Record Selection I have:
 {PatientTbl.group} in ["respMonitor","case mgmt", "test1","test2","test3"]

I also created a formula field:
 If {PatientTbl.group} in ["test1", "test2","test3"] Then
      {%testCM}

Should there be an Else-block in the formula field to print out the rest?

I don't see how to sum up only the costs for group "test" while leaving the other groups to still be printed out individually. Not sure where to go from here.

Thanks, John

Answer 1

I don't think you need a SQL expression. Just add this formula and use it as your group expression and then sum the costs. A group with one cost will still show the right value.

if {PatientTbl.group} in ["test1", "test2","test3"] Then
    "Test1-3"
else
    {PatientTbl.group}