Re: Query construction

Steve: I tried your method which is pretty much what I was trying already, but got the following error message: MySQL Error Info 1064: You have an error in your SQL syntax near 'distinct(category)) AS total FROM posts' at line 1 I am using MySQL 3.22.32 (Quite an oldish version - not sure if it allows the embedded 'distinct' clause within 'count()'...) Any advance in this...??! Many thnaks for your efforts so far.. Cheers. Russ On Tue, 21 Aug 2001 11:51:29 +0100 Steve Brett <steve.brett@e-mis.com> wrote: > select count(distinct(category)) as number_of_posts from posts; > > should do it. > > Steve > > "Russ Michell" <r.j.michell@apu.ac.uk> wrote in message > news:SIMEON.10108211219.F@k1c. anglia.ac.uk... > > Hi there people: > > > > I have a MySQL table consisting of category names among other things. > > I would like to be able to construct a query that tells me the number of > times a unique category > > name occurs in the table: > > > > file://Table 'posts' from command-line > > mysql> explain posts; > > +----------+-------------+------+-----+------------+----------------+ > > | Field | Type | Null | Key | Default | Extra | > > +----------+-------------+------+-----+------------+----------------+ > > | id | tinyint(3) | | PRI | 0 | auto_increment | > > | category | varchar(25) | | | | | > > | user | varchar(12) | | | | | > > | dateFrom | date | | | 0000-00-00 | | > > | dateTo | date | | | 0000-00-00 | | > > | title | varchar(25) | | | | | > > | body | text | | | NULL | | > > +----------+-------------+------+-----+------------+----------------+ > > 7 rows in set (0.00 sec) > > > > file://Table 'posts' in phpMyAdmin tab > > id category user dateFrom dateTo title body > > 3 some stuff maurice 2001-09-23 2001-11-05 Maurcie's post this > is Maurice's test > > 4 russ category russmichell 2001-08-17 2001-08-27 Russ is theone this > is another test > > 9 russ category russmichell 2001-08-16 2001-08-19 Begon by Monday! This > entry should dissapear > > 10 russ category russmichell 2001-08-16 2001-09-04 testing This > is another test! > > > > For example I would like to know how many posts there are in the category > 'russ category' (Should > > tell me there are 3 and only 1 for 'some stuff' ... > > > > Can anyone help me toward a nice SQL query that would to this for me!? > > Cheers all. > > > > Russ > > > > #-------------------------------------------------------# > > > > "Believe nothing - consider everything" > > > > Russ Michell > > Anglia Polytechnic University Webteam > > > > e: r.j.michell@apu.ac.uk > > w: www.apu.ac.uk/webteam > > t: +44 (0)1223 363271 x 2331 > > > > www.theruss.com > > > > #-------------------------------------------------------# > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net > For additional commands, e-mail: php-db-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net > #-------------------------------------------------------# "Believe nothing - consider everything" Russ Michell Anglia Polytechnic University Webteam e: r.j.michell@apu.ac.uk w: www.apu.ac.uk/webteam t: +44 (0)1223 363271 x 2331 www.theruss.com #-------------------------------------------------------#