Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 count distinct

Author  Topic 

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-23 : 22:18:55
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 d
left join tbljobitems j on j.docrefid=d.docrefid
where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'

will return me

docrefid
DC01C06027

but when i try to count(*) only the distinct record, it count all..

select distinct d.docrefid, count(*) from tbljobDocuments d
left join tbljobitems j on j.docrefid=d.docrefid
where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'
group by d.docrefid

will return me

DocrefId Count
DC01C06027 2

when i expect

DocrefId Count
DC01C06027 1




~~~Focus on problem, not solution~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-23 : 22:22:52
use count(distinct colname)
what is the column name that you want to count distinct ?


select d.docrefid, count(distinct <column name> )
from tbljobDocuments d
left join tbljobitems j on j.docrefid=d.docrefid
where d.docrefid = 'DC01C06027'
and j.itemid = '39J1667-H86700'
group by d.docrefid



KH

Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-23 : 22:29:35
hi,

use count(distinct colname),
is it possible to use more than one coulumn? as far as i know the count only accept one argument? humm??

~~~Focus on problem, not solution~~~
Go to Top of Page

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-03-23 : 22:32:35
forget it.. khtan!!! it worked.. thank u!!!!!!!!!!!!!!!!!!!!!!!'so we just put what distinct coulmn name into the count argument.. yeayy im so happy... keh keh keh

~~~Focus on problem, not solution~~~
Go to Top of Page
   

- Advertisement -