Need help getting count of recs within each row.

i have report based on below query.  returns row/loan number every row based on select criteria includes stat_code.  want add column give count of how many loans have loan number code (not code reporting on) e.g.  if loan 123 has 2 records, 1 stat_code , stat_code c want count column 2.  best way this.  cannot count in main query because looking @ stat_code 'a' in clause.

 

select [age]
      ,[loan_number]
      ,[prin_loan_bal]
      ,[bank]
      ,[stat_code]
      ,[booking_date]
  [dbname].[dbo].[mtsr_loan_rpt]
substring(age,1,1) in (@age)
and stat_code in ('a')
, bank in (@bank)

(i want count of loan numbers stat_code)

i hope explained properly.  appreciate ideas has.

hi,

you need self join on table in order count of of stat_codes since excluding rows existing clause.

assuming loan number unique, here's how it:

select mlr.age
      ,mlr.loan_number
      ,mlr.prin_loan_bal
      ,mlr.bank
      ,mlr.stat_code
      ,mlr.booking_date
      ,total_codes = count(distinct stat.stat_code)
  dbname.dbo.mtsr_loan_rpt mlr inner join
       dbname.dbo.mtsr_loan_rpt stat on (mlr.loan_number = stat.loan_number)
substring(mlr.age,1,1) in (@age)
, mlr.stat_code in ('a')
, mlr.bank in (@bank)
    group by
    mlr.age
      ,mlr.loan_number
      ,mlr.prin_loan_bal
      ,mlr.bank
      ,mlr.stat_code
      ,mlr.booking_date

from reading request, it's unclear me if want count of loan_numbers or count of distinct stat_codes.  so, may want remove word distinct stat.stat_code , replace count( *) statement.

good luck,

--dan

---

djansc

SQL Server  >  SQL Server Reporting Services, Power View