Select ValueA where [count(ValueB) > 2 and the counted rows share ValueC]-CodePudding

I have a table such as this:

 ------------- ----------------- ----------- 
| AggregateId | TransactionDate | EventName |
 ------------- ----------------- ----------- 
| 123abc      | 2021-08-21      | EventA    |
| 123abc      | 2021-08-21      | EventA    |
| 123abc      | 2021-08-21      | EventB    |
| 123abc      | 2021-09-15      | EventA    |
| 456xyz      | 2021-08-21      | EventA    |
| 456xyz      | 2021-08-21      | EventA    |
| 456xyz      | 2021-08-21      | EventC    |
| 456xyz      | 2021-08-30      | EventA    |
| 789dfg      | 2021-08-30      | EventA    |
| ....        | ...             | ...       |
 ------------- ----------------- -----------

I want to get a list of all AggregateIds that have more than one EventA on the same date, such is the case on the row 1,2 and 5,6.

Expected Output:

 ------------- 
| AggregateId |
 ------------- 
| 123abc      |
| 456xyz      |
 -------------

I tried something like this, but it doesn't work at all.

SELECT 
t.AggregateId
from Table1 t1 inner join Table1 t2 on t1.CreditId = t2.CreditId
where  t1.TransactionDate = t2.TransactionDate
group by t1.creditid
having count(case t.EventName when 'EventA' then 1 else null end) > 1

CodePudding user response：

try this

SELECT DISTINCT t.AggregateId, 
from Table1
where t.EventName = 'EventA'
group by t.AggregateId, t.TransactionDate
having count(*) > 1