p2p.wrox.com: Archives - asp

asp_databases thread: SELECT COUNT(DISTINCT(field)) help!

Message #1 by "Michael Filip \(Yahoo\)" <mzcfilip@y...> on Fri, 23 Mar 2001 13:19:44 -0500

Hi there,



I'm trying to count the records returned by a DISTINCT select statement, and it just ain't working!

I'm querying and Access database through an ASP page using this connection string :



PROVIDER=Microsoft.Jet.OLEDB.4.0;



I've tried each of the following, with no success.

1) SQLcommand="SELECT COUNT (DISTINCT(branch)), MBNAME, phone, COFAX FROM members WHERE " &

WhereClause & OrderByClause



2) SQLcommand="SELECT DISTINCT(branch), COUNT(branch), MBNAME, phone, COFAX FROM members WHERE " ...



3) SQLcommand="SELECT DISTINCT(branch), COUNT(branch) AS thecount, MBNAME, phone, COFAX FROM members

WHERE " ...





nothing I do seems to work... please help!



thanks,



Michael Filip

Toronto Real Estate Board

Internet Specialist

(xxx) xxx-xxxx  x8488







_________________________________________________________



Do You Yahoo!?



Get your free @yahoo.com address at http://mail.yahoo.com

Message #2 by "Owen Mortensen" <ojm@a...> on Fri, 23 Mar 2001 14:54:28 -0700

You need a group by clause for distinct to work right (in my limited

experience anyway).



Owen



-----Original Message-----

From: Michael Filip (Yahoo) [mailto:mzcfilip@y...]

Sent: Friday, March 23, 2001 7:51 PM

To: ASP Databases

Subject: [asp_databases] SELECT COUNT(DISTINCT(field)) help!





Hi there,



I'm trying to count the records returned by a DISTINCT select statement, and

it just ain't working!

I'm querying and Access database through an ASP page using this connection

string :



PROVIDER=Microsoft.Jet.OLEDB.4.0;



I've tried each of the following, with no success.

1) SQLcommand="SELECT COUNT (DISTINCT(branch)), MBNAME, phone, COFAX FROM

members WHERE " &

WhereClause & OrderByClause



2) SQLcommand="SELECT DISTINCT(branch), COUNT(branch), MBNAME, phone, COFAX

FROM members WHERE " ...



3) SQLcommand="SELECT DISTINCT(branch), COUNT(branch) AS thecount, MBNAME,

phone, COFAX FROM members

WHERE " ...





nothing I do seems to work... please help!



thanks,



Michael Filip

Toronto Real Estate Board

Internet Specialist

(xxx) xxx-xxxx  x8488

Message #3 by Imar Spaanjaars <Imar@S...> on Sat, 24 Mar 2001 11:58:16 +0100

AFAIK, you can't select multiple fields for your result set. COUNT will 

return a one column - one row resultset containing the actual number of 

records that match your query.

So:



         SELECT DISTINCT(branch), COUNT(branch), MBNAME, phone



doesn't make sense, I think. What would the columns MBNAME and phone contain??



Change it to:



Select Count (distinct branch) FROM members where etc etc etc



Hope this helps,



Imar







At 06:50 PM 3/23/2001 -0800, you wrote:

>Hi there,

>

>I'm trying to count the records returned by a DISTINCT select statement, 

>and it just ain't working!

>I'm querying and Access database through an ASP page using this connection 

>string :

>

>PROVIDER=Microsoft.Jet.OLEDB.4.0;

>

>I've tried each of the following, with no success.

>1) SQLcommand="SELECT COUNT (DISTINCT(branch)), MBNAME, phone, COFAX FROM 

>members WHERE " &

>WhereClause & OrderByClause

>

>2) SQLcommand="SELECT DISTINCT(branch), COUNT(branch), MBNAME, phone, 

>COFAX FROM members WHERE " ...

>

>3) SQLcommand="SELECT DISTINCT(branch), COUNT(branch) AS thecount, MBNAME, 

>phone, COFAX FROM members

>WHERE " ...

>

>

>nothing I do seems to work... please help!

>

>thanks,

>

>Michael Filip

>Toronto Real Estate Board

>Internet Specialist

>(xxx) xxx-xxxx  x8488

>

Message #4 by "Dallas Martin" <dmartin@z...> on Sat, 24 Mar 2001 08:02:44 -0500

Using count() with multiple columns is an ideal way to check for duplicate

entries:



SELECT fld1, fld2,COUNT(*) FROM table1

GROUP BY fld1,fld2

HAVING COUNT(*) > 1



Will let you know if there are repeating subsets of data.

This type of query is also useful for analyzing data.











----- Original Message -----

From: "Imar Spaanjaars" <Imar@S...>

To: "ASP Databases" <asp_databases@p...>

Sent: Saturday, March 24, 2001 5:58 AM

Subject: [asp_databases] Re: SELECT COUNT(DISTINCT(field)) help!





> AFAIK, you can't select multiple fields for your result set. COUNT will

> return a one column - one row resultset containing the actual number of

> records that match your query.

> So:

>

>          SELECT DISTINCT(branch), COUNT(branch), MBNAME, phone

>

> doesn't make sense, I think. What would the columns MBNAME and phone

contain??

>

> Change it to:

>

> Select Count (distinct branch) FROM members where etc etc etc

>

> Hope this helps,

>

> Imar

>

>

>

> At 06:50 PM 3/23/2001 -0800, you wrote:

> >Hi there,

> >

> >I'm trying to count the records returned by a DISTINCT select statement,

> >and it just ain't working!

> >I'm querying and Access database through an ASP page using this

connection

> >string :

> >

> >PROVIDER=Microsoft.Jet.OLEDB.4.0;

> >

> >I've tried each of the following, with no success.

> >1) SQLcommand="SELECT COUNT (DISTINCT(branch)), MBNAME, phone, COFAX FROM

> >members WHERE " &

> >WhereClause & OrderByClause

> >

> >2) SQLcommand="SELECT DISTINCT(branch), COUNT(branch), MBNAME, phone,

> >COFAX FROM members WHERE " ...

> >

> >3) SQLcommand="SELECT DISTINCT(branch), COUNT(branch) AS thecount,

MBNAME,

> >phone, COFAX FROM members

> >WHERE " ...

> >

> >

> >nothing I do seems to work... please help!

> >

> >thanks,

> >

> >Michael Filip

> >Toronto Real Estate Board

> >Internet Specialist

> >(xxx) xxx-xxxx  x8488

Message #5 by Imar Spaanjaars <Imar@S...> on Sat, 24 Mar 2001 14:55:48 +0100

Whooops, my mistake. Of course you are right.. Must have been a bit sleepy 

or distracted.



Sorry for the misleading information.





Imar





At 08:02 AM 3/24/2001 -0500, you wrote:

>Using count() with multiple columns is an ideal way to check for duplicate

>entries:

>

>SELECT fld1, fld2,COUNT(*) FROM table1

>GROUP BY fld1,fld2

>HAVING COUNT(*) > 1

>

>Will let you know if there are repeating subsets of data.

>This type of query is also useful for analyzing data.

>

>

>

>

>

>----- Original Message -----

>From: "Imar Spaanjaars" <Imar@S...>

>To: "ASP Databases" <asp_databases@p...>

>Sent: Saturday, March 24, 2001 5:58 AM

>Subject: [asp_databases] Re: SELECT COUNT(DISTINCT(field)) help!

>

>

> > AFAIK, you can't select multiple fields for your result set. COUNT will

> > return a one column - one row resultset containing the actual number of

> > records that match your query.

> > So:

> >

> >          SELECT DISTINCT(branch), COUNT(branch), MBNAME, phone

> >

> > doesn't make sense, I think. What would the columns MBNAME and phone

>contain??

> >

> > Change it to:

> >

> > Select Count (distinct branch) FROM members where etc etc etc

> >

> > Hope this helps,

> >

> > Imar

> >

> >

> >

Return to Index

P2P Archives