CODEWITHSUNDEEP
sqldistinct
user2811136
user2811136

Reputation: 23

COUNT(DISTINCT) in subquery

I am trying to retrieve each loan number, with the count of how many times the loan number appears in each month. So in some cases the loan number appears twice in the month, but I just want it to be counted once in each month. TEST and RD columns are not giving me what I need. Here is what I have:

select 
[Month],
one,
LoanNumber,
two,
three,
four,
...

count(distinct LoanNumber) as TEST,
COUNT(LoanNumber) OVER () as RD

from

tableNAME LC
where [month] ='12-2016'
group by 
[Month],
one,
LoanNumber,
two,
three,
four,
...

Please help!

Upvotes: 1

Views: 5161

Answers (2)

SlimsGhost
SlimsGhost

Reputation: 2909

I think you just need to add a partition by clause to your windowing count() expression, and remove the group by, like this:

select 
[Month],
one,
LoanNumber,
two,
three,
four,
...
COUNT(*) OVER (partition by Month, LoanNumber) as NumberOfOccurences

from

tableNAME LC
where [month] ='12-2016'

Upvotes: 3

Beth
Beth

Reputation: 9607

you need to take LoanNumber out of your GROUP BY clause.

Also, take it out of your SELECT clause, if you want to group by month, and not each loanNumber within the month.

Upvotes: 0

Related Questions