COUNT (DISTINCT (myfield)) PROBLEM

9 Dec 2005 5:08 PM

Savas Ates

it works well

SELECT DISTINCT ([cinsiyet]) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';

but i need to get recordcount of this query whis oledb.datareader doesnt say
it to me...

SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';

it returns error ? how can i get this query resultset ?

9 Dec 2005 5:15 PM

Aaron Bertrand [SQL Server MVP]

> it returns error ? how can i get this query resultset ?

What error? Are you referring to the column name in your .NET code? If so,
you'll need to add an alias, and refer to that.

SELECT Count_cinsiyet = COUNT(DISTINCT cinsiyet)
    FROM URUNLER
    WHERE cinsiyet NOT IN
        ('Bileklik' , 'Yuzuk' , 'Set' , 'Kupe' , 'Bilezik' , 'Kolye');

Now, refer to reader["Count_cinsiyet"];

Or, refer to the ordinal number instead of the name.

9 Dec 2005 5:23 PM

Savas Ates

it is an query error.. my db is an access database.. I tried it as an access
qýuery in access it returns error ?

"Savas Ates" <sa***@indexinteractive.com>, haber iletisinde þunlarý

yazdý:%23cB1tMO$FHA.1***@TK2MSFTNGP10.phx.gbl...
Show quote

> it works well
>
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
>
> but i need to get recordcount of this query whis oledb.datareader doesnt
> say it to me...
>
> SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
>
> it returns error ? how can i get this query resultset ?
>

9 Dec 2005 5:33 PM

Aaron Bertrand [SQL Server MVP]

> it is an query error.

Can you be MORE SPECIFIC?

> qýuery in access it returns error ?

Can you be MORE SPECIFIC?

9 Dec 2005 5:46 PM

Savas Ates

it works well in SQL Database.. But when i try it in access it returns
error. it says there is missing operator in this query ?

how can i solve it ?

"Savas Ates" <sa***@indexinteractive.com>, haber iletisinde þunlarý

yazdý:%23cB1tMO$FHA.1***@TK2MSFTNGP10.phx.gbl...
Show quote

> it works well
>
> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
>
> but i need to get recordcount of this query whis oledb.datareader doesnt
> say it to me...
>
> SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
>
> it returns error ? how can i get this query resultset ?
>

9 Dec 2005 5:55 PM

Aaron Bertrand [SQL Server MVP]

> it works well in SQL Database.. But when i try it in access it returns
> error.

Egads. WHAT ERROR?

9 Dec 2005 6:20 PM

dan artuso

I'm not 100% sure, but I don't think Access supports Count(Distinct())

--

HTH
Dan Artuso

Show quote

"Savas Ates" <sa***@indexinteractive.com> wrote in message
news:O4wbFiO$FHA.208@tk2msftngp13.phx.gbl...
> it works well in SQL Database.. But when i try it in access it returns
> error. it says there is missing operator in this query ?
>
> how can i solve it ?
>
>
> "Savas Ates" <sa***@indexinteractive.com>, haber iletisinde þunlarý
> yazdý:%23cB1tMO$FHA.1***@TK2MSFTNGP10.phx.gbl...
>> it works well
>>
>> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
>> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
>> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
>>
>> but i need to get recordcount of this query whis oledb.datareader doesnt
>> say it to me...
>>
>> SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
>> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
>> cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye';
>>
>> it returns error ? how can i get this query resultset ?
>>
>
>

9 Dec 2005 6:35 PM

Aaron Bertrand [SQL Server MVP]

> I'm not 100% sure, but I don't think Access supports Count(Distinct())

It sure would be nice to see the actual error message. I'm not holding my
breath...

9 Dec 2005 7:44 PM

Bob Barrows [MVP]

Aaron Bertrand [SQL Server MVP] wrote:

>> I'm not 100% sure, but I don't think Access supports
>> Count(Distinct())
>
> It sure would be nice to see the actual error message. I'm not
> holding my breath...

He did say "missing operator" ... :-)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

9 Dec 2005 7:46 PM

Bob Barrows [MVP]

Dan is correct: JetSQL does not support COUNT(DISTINCT ...)

What you have to do is use a subquery (aka derived table):

Select Count(*) From
(SELECT DISTINCT ([cinsiyet]) FROM URUNLER
WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set' AND
cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<> 'Kolye') As q

Bob Barrows

Savas Ates wrote:
Show quote

> it works well in SQL Database.. But when i try it in access it returns
> error. it says there is missing operator in this query ?
>
> how can i solve it ?
>
>
> "Savas Ates" <sa***@indexinteractive.com>, haber iletisinde þunlarý
> yazdý:%23cB1tMO$FHA.1***@TK2MSFTNGP10.phx.gbl...
>> it works well
>>
>> SELECT DISTINCT ([cinsiyet]) FROM URUNLER
>> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set'
>> AND cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<>
>> 'Kolye'; but i need to get recordcount of this query whis
>> oledb.datareader
>> doesnt say it to me...
>>
>> SELECT COUNT ( DISTINCT ([cinsiyet]) ) FROM URUNLER
>> WHERE cinsiyet<>'Bileklik' AND cinsiyet<>'Yuzuk' AND cinsiyet<>'Set'
>> AND cinsiyet<> 'Kupe' AND cinsiyet<>'Bilezik' AND cinsiyet<>
>> 'Kolye'; it returns error ? how can i get this query resultset ?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"