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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 count distinct
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maya_zakry
Constraint Violating Yak Guru

Malaysia
379 Posts

Posted - 03/23/2007 :  22:18:55  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
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)

Singapore
17689 Posts

Posted - 03/23/2007 :  22:22:52  Show Profile  Reply with Quote
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

Malaysia
379 Posts

Posted - 03/23/2007 :  22:29:35  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
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

Malaysia
379 Posts

Posted - 03/23/2007 :  22:32:35  Show Profile  Click to see maya_zakry's MSN Messenger address  Send maya_zakry a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000