Prev: Default date format for convert(datetime,TextDate)
Next: how to strip specific email address from nvarchar field with m
From: Andy on 2 Jul 2008 10:25
Hi All,

I need some help with distinct count on mulitple field e.g
Select count (distinct Field1, Field2, Field3, Field4, Field5, Field6,
Field7, Field8, Field8)
from tableA
Where Field1 = 'somecondition'

Thanks
A
From: Plamen Ratchev on 2 Jul 2008 10:34
You can use derived table:

SELECT COUNT(*)
FROM (SELECT DISTINCT col1, col2, col3
FROM Foo
WHERE col1 = 'somecondition') AS T;

HTH,

Plamen Ratchev
http://www.SQLStudio.com
From: Uri Dimant on 2 Jul 2008 10:41
Andy
select top (1) count(*) over ()

from

(

select

distinct f1,f2,f3,f4,........from tblname

) as d

"Andy" <Andy(a)discussions.microsoft.com> wrote in message
news:6C7FD95B-B465-42F4-9471-05D53179A15D(a)microsoft.com...
> Hi All,
>
> I need some help with distinct count on mulitple field e.g
> Select count (distinct Field1, Field2, Field3, Field4, Field5, Field6,
> Field7, Field8, Field8)
> from tableA
> Where Field1 = 'somecondition'
>
> Thanks
> A


From: Uri Dimant on 2 Jul 2008 10:52
You are right Plamen. I should not add COUNT(*) OVER() as I did in my query



"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:A22CC0DD-5A1F-4BCA-A6CC-227D007C7F37(a)microsoft.com...
> You can use derived table:
>
> SELECT COUNT(*)
> FROM (SELECT DISTINCT col1, col2, col3
> FROM Foo
> WHERE col1 = 'somecondition') AS T;
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com


From: --CELKO-- on 2 Jul 2008 11:25
>> I need some help with distinct count on multiple fields [sic: columns are not fields e.g <<

Your first problem is that you don't know the differences between
fields and columns; you are still writing file system code and not
real SQL. That error leads you to think that your invented syntax
will work as if you were still writing COBOL, where contiguous storage
would let you combine multiple fields as one unit of work.

SELECT COUNT (DISTINCT field1, field2, field3, field4, field5, field6,
field7, field8, field8)
FROM TableA
WHERE field1 = 'somecondition';

The COUNT([DISTINCT] <exp>) function takes a set of expressions,
removes the NULLs, then optionally removes redundant duplicates
returns the cardinality of the set. The function has one and only one
parameter. What did you expect to happen?

 |  Next  |  Last
Pages: 1 2
Prev: Default date format for convert(datetime,TextDate)
Next: how to strip specific email address from nvarchar field with m