How to filter GROUP BY results? HAVING is not producing desired results

Question

I have a query that produces results like below. What I want to do is filter the results to include only where there are more than 1 TRANS per day. In the below, it should result in rows 3, 4 , 6, and 7.

How can I do that filtering?

Row	DATE	TRANS	DESCR	AMOUNT
1	12/11/2002	BUY	Positive	100
2	12/18/2002	BUY	Positive	100
3	12/20/2002	BUY	Positive	100
4	12/20/2002	SELL	Negative	-100
5	12/23/2002	BUY	Positive	100
6	12/24/2002	BUY	Positive	100
7	12/24/2002	SELL	Negative	-100

SELECT 
    to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy') as DATE,
    trim(CA.TRANS) as TRANS,
    CA.DESCR,   
    sum(CA.AMOUNT) 
FROM 
    CASHDBO.TRANSACTIONS CA
    INNER JOIN SECURITYDBO.SECURITY SM  ON (SM.SECURITY_ID = CA.SECURITY_ID) 
WHERE
    CA.TRANS_TYPE in ( 'REINVEST','BUY','RGHTEXER','SELL')
GROUP BY
    to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy'),
    trim(CA.TRANS),
    CA.DESCR

I have tried adding

HAVING 
    COUNT(trim(CA.TRANS)) > 1

But that filtered based on the underlying/non-grouped records.

Answer 1

You can use your query and work with it:

WITH ( ... ) AS your_query
SELECT *
FROM your_query
WHERE date IN 
(
  SELECT date
  FROM your_query
  GROUP BY date
  HAVING COUNT(*) > 1
);

Or add a window function to your query and use that:

SELECT date, trans, descr, total
FROM
(
  SELECT 
    TO_CHAR(ca.effective_date, 'mm/dd/yyyy') AS date,
    TRIM(ca.trans) AS trans,
    ca.descr,   
    SUM(ca.amount) AS total,
    COUNT(*) OVER (PARTITION BY TO_CHAR(ca.effective_date, 'mm/dd/yyyy')) AS cnt
  FROM ...
) with_cnt
WHERE cnt > 1;