SQL Count Aggregate with Multiple Having Conditions

Question

I am attempting to write a SQL query that only returns employees that have multiple transactions on the same date (count(TransactionDate) > 1), but the transactions occurred within different store IDs. I am attempting to use a combination of the count aggregate and having, but can't seem to return the correct values. Would a temp table be a better way to do it, or maybe a sub query? My below query is not returning accurate records. Any help is appreciated. Thanks!

EmployeeID  | StoreID | TransactionDate
--------------------------------------
     1      |   1     | 2016-09-09    --should be returned
--------------------------------------
     1      |   2     | 2016-09-09    --should be returned
--------------------------------------
     1      |   3     | 2016-09-09    --should be returned
--------------------------------------
     1      |   1     | 2016-09-18    --should not be returned
--------------------------------------
     2      |   1     | 2016-09-09    --should not be returned
--------------------------------------
     2      |   1     | 2016-09-09    --should not be returned
--------------------------------------
     3      |   1     | 2016-09-09    --should not be returned
--------------------------------------
     4      |   5     | 2016-09-09    --should be returned
 --------------------------------------
     4      |   6     | 2016-09-09    --should be returned

select top 1000 EmployeeID, StoreID, TransactionDate, count(StoreID)[StoreCount], count(TransactionDate)[Transaction Count]
from myTable  
group by EmployeeID, StoreID, TransactionDate
having count(StoreID) > 1 and count(TransactionDate) > 1
order by TransactionDate desc

which rdbms? sql-sever, mysql, oracle,...? It makes a difference because this is a perfect example for window functions — Matt
– Matt, Commented Sep 29, 2016 at 0:13

Matt · Accepted Answer · 2016-09-29 00:27:44Z

1

SELECT t.*
FROM
    (
       SELECT
          EmployeeId, TransactionDate
       FROM
          Table
       GROUP BY
          EmployeeId, TransactionDate
       HAVING
          COUNT(DISTINCT StoreId) > 1
    ) e
    INNER JOIN Table t
    ON e.EmployeeId = t.EmployeeId
    AND e.TransactionDate = t.TransactionDate

Actually window functions wouldn't be a lot of help here because the key is to COUNT(DISTINCT StoreId) Grouped by Employee & TransactionDate and COUNT(DISTINCT ) OVER () is not allowed. So the derived table is the way to go and this syntax will work with pretty much all of the typical RDBMS.

edited Sep 29, 2016 at 0:27

answered Sep 29, 2016 at 0:20

Matt

14.4k2 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2016-09-29 01:25:26Z

0

If you just want the employees:

SELECT DISTINCT EmployeeId
FROM myTable t
GROUP BY EmployeeId, TransactionDate
HAVING MIN(StoreId) <> MAX(StoreId);

This is one of the very few situations where select distinct is used with group by. So this type of query is a rather special thing.

answered Sep 29, 2016 at 1:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL Count Aggregate with Multiple Having Conditions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related