Conditional Sum

[pblazeppd]

Hi all,

Version: CR9
Connection: ODBC

Tables: INMAST_VIEW, Primay Key: Case#
INPER6_VIEW (fields relavent: involvement, means of attack)
INMOON_VIEW

LEFT outer joined

I need to Sum, means of attack from INPER6_VIEW, where involvment = VIC. There may be many victims related to each case#. The only grouping is on INMAST_VIEW.Case#.

If I have

Case#06-1234
INVOLMENT: VIC NAME: Doe,John MEANS_of_Attack: K
INVOLMENT: VIC NAME: Doe,Jane MEANS_of_Attack: F
INVOLMENT: SUS NAME: Pate,Norm MEANS_of_Attack: <NULL>

Case#06-2345
INVOLMENT: VIC NAME: Doe,KIM MEANS_of_Attack: O
INVOLMENT: VIC NAME: Doe,Kline MEANS_of_Attack: K
INVOLMENT: SUS NAME: Doe,Jane MEANS_of_Attack: <NULL>

Case#06-3456
INVOLMENT: VIC NAME: Doe,Larry MEANS_of_Attack: S
INVOLMENT: SUS NAME: Doe,Jane MEANS_of_Attack: <NULL>

MEANS OF ATTACK Codes:
F - Firearm
H - Hands / Feet / Fists
K - Knife / Cutting Instrument
O - Other Dealy Weapon
S - Simple / Non-Deadly Instrument
T - Threats

OUTPUT in Chart:
Firarm 1
Knife 2
Other Deadly Instrument 1
Simple / Non-Deadly Instrument 1

Thanks for any and all help is appreciated.

Phil

 

[IdoMillet]

Create a formula: {@K_1_0}

IF IsNull({Means of Attack}) THEN 0 
ELSE IF {Means of Attack}="K" THEN 1 ELSE 0

Sum (note: not count) that formula to get tyhe count of K's

Do the same for the other codes.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com