sql - wrong number in count() -

i've posted 2 questions problem , i'm there think.

these older posts:

1. use array/variable in sql-query
2. extend sql result - add row if not exist

it easier explain example, situation fictive example:

my table looks (i show rows need, table contains +100k rows)

status  pgid    nvarchar5               nvarchar10  catid   tp_id   isactive      null    information technology  null        1       1       1 hr      null    human recource          null        1       2       1 fin     null    finance                 null        1       3       1 new        1    null                    1354        2       10001   1 new        1    null                    464         2       10002   1 new        1    null                    13465       2       10003   1 active     1    null                    79846       2       10004   1 deleted    1    null                    132465      2       10005   1 new        2    null                    79847       2       10006   1 new        2    null                    341         2       10007   1 deleted    2    null                    465         2       10008   1 deleted    2    null                    132         2       10009   1 deleted    2    null                    465         2       10010   1 deleted    2    null                    1           2       10011   1 new        3    null                    465         2       10012   1 new        3    null                    1465        2       10013   1 new        3    null                    132         2       10014   1 null    null    null                    null        3       20136   1 null    null    null                    null        4       22165   1 null    null    null                    null        3       24566   1 

and use query:

select stat.status       ,d.[pgid] pgid       ,pg.[nvarchar5] pgname       ,count(d.[nvarchar10])      (select distinct [status]      [content].[dbo].[usrdata]        catid = '1') stat cross join       [content].[dbo].[usrdata]  d inner join       [content].[dbo].[usrdata]  pg on d.[pgid] = pg.[tp_id] d.catid = '1'       , d.[nvarchar10] not null       , stat.status not null       , d.[isactive] = 1       , pg.[catid] = '2' group stat.status,           pg.[nvarchar5],           d.[pgid] order pgname 

then result:

status  pgid    nvarchar5               total new     1       information technology  5 active  1       information technology  5 deleted 1       information technology  5 new     2       human recource          6 active  2       human recource          6 deleted 2       human recource          6 new     3       finance                 3 active  3       finance                 3 deleted 3       finance                 3 

but have result:

status  pgid    nvarchar5               total new     1       information technology  3 active  1       information technology  1 deleted 1       information technology  1 new     2       human recource          2 active  2       human recource          0 deleted 2       human recource          4 new     3       finance                 3 active  3       finance                 0 deleted 3       finance                 0 

i'm not pro in sql. maybe it's stupid i'm stuck... welcome! :)

i noticed in result you're seeking, there's less rows in total column result given, may due duplicated rows, i'm guessing cross join responsible it, why don't try inner join instead on status

(select distinct [status]      [content].[dbo].[usrdata]         catid = '1') stat       inner join [content].[dbo].[usrdata]  d on stat.[status]=d.[status]      inner join [content].[dbo].[usrdata]  pg on d.[pgid] = pg.[tp_id]