Summary Query With Case Statement

[MattSmithProg]

Hi,

I am having a little trouble attempting to coerce the data in to a format that a report needs to be in.

I have data in the format of this.

NAT Finalisation_Result
25 Dr Amend
25 Cr Amend
50 On Hold
25 On Hold
40 NFA
.
.
.
.


What I want is to have the data return like this

NAT Total Dr Amend Cr Amend On Hold NFA
25 2 1 1
40 1 1
50 1 1

etc.

I have tried this but it doesn't group correctly.

Select NAT, Count(*) As Amount,
Case When Finalisation_Result = 'Cr amend' Then Count(Finalisation_Result) Else 0 End As CrAmend,
Case When Finalisation_Result = 'Dr amend' Then Count(Finalisation_Result) Else 0 End As Dramend,
Case When Finalisation_Result = 'NFA' Then Count(Finalisation_Result) Else 0 End As NFA,
Case When Finalisation_Result = 'OnHold' Then Count(Finalisation_Result) Else 0 End As OnHold,

From PTACR.PTAPP_ACR_Inactive

Group By NAT, Finalisation_Result;

Any help would be greatly appreciated.

 

[arrowhouse]

try this:

Select NAT, Count(*) As Amount,
SUM(Case When Finalisation_Result = 'Cr amend' Then 1 Else 0 End) As CrAmend,
SUM(Case When Finalisation_Result = 'Dr amend' Then 1 Else 0 End) As Dramend,
SUM(Case When Finalisation_Result = 'NFA' Then 1 Else 0 End) As NFA,
SUM(Case When Finalisation_Result = 'OnHold' Then 1 Else 0 End) As OnHold,

From PTACR.PTAPP_ACR_Inactive

Group By NAT

 

[MattSmithProg]

Thanks arrowhouse

Works a treat.