Max function / unexpected results

Database
Enthusiast

Max function / unexpected results

My original report returns 3 records for account ABC, 1 with an approved status and 2 with a suspended status.  The only difference in these lines is the status and billing number.  All other data is identical. 

 

I'm trying to create a variation of this report that would return 1 line for Account ABC with a column that displays the count for approved accounts and another column with the count for suspended accounts. 

 

In the new report, there would be an Approved Accounts column with a value of 1 and a Suspended Accounts column with a value of 2.

 

I'm using a MAX function to return only 1 line.  The issue I'm having is that 2 records with the suspended status are identical except for the Billing Number.

 

If I remove the billing number from the SQL then the results only return 1 suspended and 1 approved account.  I need the SQL to return 1 line with 1 in the approved column and 2 in the suspended column

 

Here is some sample data:

 

Acct#    Bill#    Name    Location   Status 
ABC      Bill1    ABC Co   123456   Approved
ABC      Bill2    ABC Co   123456   Suspended
ABC      Bill3    ABC Co   123456   Suspended

Any suggestions would be greatly appreciated.  Thanks for your help.....

Tags (3)

Accepted Solutions
Junior Contributor

Re: Max function / unexpected results

You need "conditional aggregation":

select Acct#,  Name,    Location, 
   sum(case when Status = 'Approved' then 1 else 0 end) as Approved,
   sum(case when Status = 'Suspended' then 1 else 0 end) as Suspended
from ...
group by 1,2,3
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

Re: Max function / unexpected results

You need "conditional aggregation":

select Acct#,  Name,    Location, 
   sum(case when Status = 'Approved' then 1 else 0 end) as Approved,
   sum(case when Status = 'Suspended' then 1 else 0 end) as Suspended
from ...
group by 1,2,3
Enthusiast

Re: Max function / unexpected results

Thank you very much.  That did exactly what I was looking for..........