我有一个查询,它会产生如下结果.我想要做的是过滤结果,以便只包括每天有超过1个交易的情况.在下面,它应该导致第3、4、6和7行.
我怎么才能进行过滤呢?
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
我试着添加了
HAVING
COUNT(trim(CA.TRANS)) > 1
但这是根据底层/未分组的记录进行过滤的.