CODEWITHSUNDEEP
sqlfilteroracle-sqldeveloperaggregate-functions
P--
P--

Reputation: 13

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

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.

Upvotes: 1

Views: 46

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94969

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;

Upvotes: 0

NickW
NickW

Reputation: 9788

If you want to restrict transactions to where there are more than one in a day then something like this should work:

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')
    AND EXISTS (SELECT CA1.EFFECTIVE_DATE
                FROM CASHDBO.TRANSACTIONS CA1
                WHERE CA1.EFFECTIVE_DATE = CA.EFFECTIVE_DATE
                GROUP BY CA1.EFFECTIVE_DATE
                HAVING COUNT(1) > 1
               )
GROUP BY
    to_char(CA.EFFECTIVE_DATE, 'mm/dd/yyyy'),
    trim(CA.TRANS_TYPE),
    CA.PERFORMANCE_FLOW_DESCR

Upvotes: 0

Related Questions