0
SELECT PERSON_ID,COUNT(APPL_ID) COUNTS
FROM APPLICATIONS
GROUP BY PERSON_ID
ORDER BY COUNTS DESC

The above query returns all persons who have applied for all time (essentially a count of the whole table). I want a clause to limit this to persons who have applied this year, yet still show the count of ALL their applications... IE, a person may have applied in 2018 once, but they've applied 20 times total thus the results would be similar. Skipping over id's 1,2, 4-11.

ID | COUNT
---+------- 
003|     20
---+------- 
012|      4

2 Answers 2

1

I suspect you can do it using a subquery, something like:

SELECT PERSON_ID, COUNT(APPL_ID) COUNTS
FROM APPLICATIONS
WHERE PERSON_ID IN (
    SELECT DISTINCT PERSON_ID
    FROM APPLICATIONS
    WHERE APPL_DATE > 2018-01-01
)
GROUP BY PERSON_ID
ORDER BY COUNTS DESC
0

Depending on indexes etc. this should provide slightly lower I/O:

SELECT PERSON_ID, COUNTS = COUNT(APPL_ID)
FROM dbo.APPLICATIONS
GROUP BY PERSON_ID
HAVING COUNT(CASE WHEN APPL_DATE >= '20180101' THEN 1 ELSE 0 END) >= 1
ORDER BY COUNTS DESC;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.