I’m looking to see often account codes (FTABLE_ACCT_CODE) from a list were used in the past 5 years. I’d like to inactivate them if they weren’t used much.
I tried the following
select distinct FTABLE_ACCT_CODE, Count(FTABLE_ACTIVITY_DATE), FTABLE_ACTIVITY_DATE
from FTable
where FTABLE_ACCT_CODE IN ('1000','1001')
and FTABLE_ACTIVITY_DATE >= '15-May-2018'
GROUP BY FTABLE_ACCT_CODE, FTABLE_ACTIVITY_DATE
I was expecting only 1 row per ACCT_CODE saying when & how many times it was last used in the past 5 years. But my code shows over 70 rows and every single time the ACCT_CODE was used in the past 5 years.
>Solution :
You don’t need both the distinct and the group by.
Also including the date is going to cause you problems. Try again as follows
select FTABLE_ACCT_CODE, Count(FTABLE_ACTIVITY_DATE)
from FTable
where FTABLE_ACCT_CODE IN ('1000','1001')
and FTABLE_ACTIVITY_DATE >= '15-May-2018'
GROUP BY FTABLE_ACCT_CODE
Not sure whether this also has meaning for you but I thought worth mentioning that count(FTABLE_ACTIVITY_DATE) can have a different result than count(*) if there are null date values in some rows. count(*) returns the count of rows, whereas count(FTABLE_ACTIVITY_DATE) returns the count of rows in which FTABLE_ACTIVITY_DATE is not null.