RE: Group by and Count query - Andrew Braithwaite - com.mysql.lists.mysql

4 messages in com.mysql.lists.mysqlRE: Group by and Count query

From	Sent On	Attachments
Karl J. Stubsjoen	11 Jun 2003 09:53
Mithun Bhattacharya	11 Jun 2003 10:04
Ryan McDougall	11 Jun 2003 10:27
Andrew Braithwaite	11 Jun 2003 14:24

Subject:	RE: Group by and Count query
From:	Andrew Braithwaite (and...@multimap.com)
Date:	06/11/2003 02:24:20 PM
List:	com.mysql.lists.mysql

You could do the following:

select host,count(*) as counthost from TABLE_HOST group by 1 having counthost = 1;

Which will list all the hosts with only 1 record..

Then get the "numrows" from that query to find uot how many there are..

Cheers,

Andrew

-----Original Message----- From: Mithun Bhattacharya [mailto:mith...@ebookers.com] Sent: Wednesday 11 June 2003 18:05 To: MySQL Mailing List Subject: Re: Group by and Count query

What about

SELECT COUNT(DISTINCT(host)) FROM TABLE_HOST WHERE path_a="";

Mithun

Karl J. Stubsjoen wrote:

Hello, I am trying to count the number of unique records that exist in my database based on a given criteria. My table looks like this:

TABLE_HOST id host path_a path_b

The field host will have duplicates like:

myway.com hisway.com hisway.com hisway.com someway.com someway.com yourway.com

The given criteria is: only count the records where path_a is blank (path_a='')

Here is the query I'd like to run (which fails - but is exactly what I need for a query): select sum(count(host)) from TABLE_HOST where path_a='' group by host

This is the next query I tried... (nothing distinct about the result of a count) select distinct count(host) from TABLE_HOST where path_a=''

So, is it possible to perform this query? Karl