1

I am trying to count the number of two types of transactions in a column and to return a value of 0 if there are none. The table is Transaction, the columns I want returned are the account number and a column for the count each of the two transactions listed. here's what I have, but it doesn't run:

SELECT 
ACCTNBR, 
COUNT(CASE when RTXNTYPCD='XDEP'then 1 else 0) AS Deposits,
COUNT(CASE when RTXNTYPCD='PWTH'then 1 else 0) AS Debits

FROM  
TRANSACTION 

WHERE 
((POSTDATE BETWEEN TO_DATE('05-01-2014','MM-DD-YYYY')) AND TO_DATE('05-31-2014','MM-DD-YYYY')) 

and ACCTNBR in ( 406,   1206,   1347,   4556,   6668,   9845)

GROUP BY 
ACCTNBR
Improve this question
1
  • what error are u getting? is it maybe related to adding a space before each of your THEN keywords? Or maybe adding an END to the CASE? ;) Commented Jun 6, 2014 at 21:54

1 Answer 1

Reset to default
1

The count function counts any value that isn't null. Since both 0 and 1 are not null, both your counts will return the total number of rows in your table. Instead, you should use sum:

SELECT 
 ACCTNBR, 
 SUM(CASE when RTXNTYPCD='XDEP' then 1 else 0 END) AS Deposits,
 SUM(CASE when RTXNTYPCD='PWTH' then 1 else 0 END) AS Debits
FROM  
 TRANSACTION 
WHERE 
 ((POSTDATE BETWEEN TO_DATE('05-01-2014','MM-DD-YYYY')) AND 
                     TO_DATE('05-31-2014','MM-DD-YYYY')) 
   and ACCTNBR in ( 406,   1206,   1347,   4556,   6668,   9845)
GROUP BY 
 ACCTNBR
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks - but after correcting the SYN to SUM, I still get an error:ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause: *Action: Error at Line: 8 Column: 12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.