hi all,sometimes, when tables are poorly joined, we have exactly same rows as a result.. so we use distinct.. but how can we count(*) the distinct rows?select distinct d.docrefid from tbljobDocuments dleft join tbljobitems j on j.docrefid=d.docrefidwhere d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'
will return me docrefidDC01C06027
but when i try to count(*) only the distinct record, it count all..select distinct d.docrefid, count(*) from tbljobDocuments dleft join tbljobitems j on j.docrefid=d.docrefidwhere d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'group by d.docrefid
will return meDocrefId CountDC01C06027 2
when i expect DocrefId CountDC01C06027 1
~~~Focus on problem, not solution~~~