Need SQL Query to count values over multiple columns

Question

Hello!

I am trying to get my information in Wide format instead of Long and I can't figure out what I'm doing wrong.

I started with:

SELECT  
codetable.status_code,  
codetable.client_code,   
COUNT(*)  
FROM  
debt  
GROUP BY codetable.client_code, codetable.status_code  

Which is what gave me the long format. 3 columns - Customer Client Code, Debt Status Code, and Count

I keep receiving an error message from the system I'm using regarding the following lines of code. There were several different iterations of error message so I won't post them. I have had some success, i.e. getting wide format, but I could not get a count of each code under each 'codetable.status_code' (example, ACT, PIF, SIF, etc).

SELECT
codetable.client_code,
MAX(CASE WHEN status_code = 'WRITTEN' then status_code end) as WRITTEN,
MAX(CASE WHEN status_code = 'SONN' then status_code end) as SONN,
MAX(CASE WHEN status_code = 'SIP' then status_code end) as SIP,
MAX(CASE WHEN status_code = '1099C_SENT' then status_code end) as "1099C_SENT",
MAX(CASE WHEN status_code = 'ACT' then status_code end) as ACT,
MAX(CASE WHEN status_code = 'ALLE' then status_code end) as ALLE,
MAX(CASE WHEN status_code = 'ATTY' then status_code end) as ATTY,
MAX(CASE WHEN status_code = 'BNK' then status_code end) as BNK,
MAX(CASE WHEN status_code = 'BPR' then status_code end) as BPR,
MAX(CASE WHEN status_code = 'CAN' then status_code end) as CAN,
MAX(CASE WHEN status_code = 'DEC' then status_code end) as DEC,
MAX(CASE WHEN status_code = 'DST' then status_code end) as DST,
MAX(CASE WHEN status_code = 'DXB' then status_code end) as DXB,
MAX(CASE WHEN status_code = 'NEW' then status_code end) as NEW,
MAX(CASE WHEN status_code = 'PAY' then status_code end) as PAY,
MAX(CASE WHEN status_code = 'PIP' then status_code end) as PIP,
MAX(CASE WHEN status_code = 'PTP' then status_code end) as PTP,
MAX(CASE WHEN status_code = 'PTS' then status_code end) as PTS,
MAX(CASE WHEN status_code = 'RESOLVED' then status_code end) as RESOLVED,
MAX(CASE WHEN status_code = 'SL' then status_code end) as SL,
COUNT(*)
FROM
debt
GROUP BY codetable.client_code

At this point I don't care how long the code needs to be. I need 21 columns, including Customer Client Code and the individual client codes above and no Count column.
What I have now is 22 columns, including a Count column. I have read several articles presented on this site based on my question and I just feel like since none of them helped me specifically that I must be missing something else.

If this is enough information?

If so, how do I get the results I'm looking for?? :(

Answer 1

Try replacing all of MAX with COUNT. Remove COUNT(*).