subquery or join it dont work 1

[KOVMoe]

I want to count a column several ways.

select v.loc_na, v.loc_nr,
count(n.nit_pr_cml_nr)as counterz,
counter2 = (select count(*) from tnitnam_name c
where c.nit_pr_cml_nr <100)
from tnitnam_name n
inner join vlocref v on n.loc_sys_nr = v.loc_sys_nr
group by v.loc_nr,n.loc_sys_nr,v.loc_sys_nr,v.loc_na, n.nit_pr_cml_nr

I need the total number per v.loc_nr, then the items less than 100 per v.loc_nr.

Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[gmmastros]

Is this what you are looking for?

select  v.loc_na, 
        v.loc_nr, 
        count(n.nit_pr_cml_nr)as counterz,
        Sum(Case When c.nit_pr_cml_nr <100 Then 1 Else 0 End) As Counter2
from    tnitnam_name n
        inner join vlocref v on n.loc_sys_nr = v.loc_sys_nr
group by v.loc_nr,n.loc_sys_nr,v.loc_sys_nr,v.loc_na, n.nit_pr_cml_nr

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[KOVMoe]

Some times stepping away from a problem is the best way to solve the problem. Thanks for the answer. That was it - exactly

Thank you,

Moe-King of the Village Idiots.

"When in trouble,
when in doubt;
Run in circles-
SCREAM & SHOUT!!"
Burma Shave

 

[gmmastros]

Glad to help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom