[PHP-DB] Re: Query construction
select count(*), category from posts group by category or select count(*) from posts group by category where category = 'foobar' should do the trick. regards, mike. -- mfG, Michael Kumar (michael.kumar_at_nme.at, m.kumar_at_hushmail.com) PGP: 30FE 3AB9 5077 C554 5D9E B5CC 0B0F CFB2 55D6 AA75 .: know your nme :. In article SIMEON.10108211219.F@k1c. anglia.ac.uk, [EMAIL PROTECTED] says... 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: //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| | | -00-00 || | dateTo | date| | | -00-00 || | title| varchar(25) | | ||| | body | text| | | NULL || +--+-+--+-+++ 7 rows in set (0.00 sec) //Table 'posts' in phpMyAdmin tab id category userdateFromdateTo titlebody 3 some stuffmaurice 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Re: Query construction
dang, messed up the order of statements, sorry. of course it must read: select count(*) from posts where category = 'foobar' group by category regards, mike. -- mfG, Michael Kumar (michael.kumar_at_nme.at, m.kumar_at_hushmail.com) PGP: 30FE 3AB9 5077 C554 5D9E B5CC 0B0F CFB2 55D6 AA75 .: know your nme :. In article [EMAIL PROTECTED], [EMAIL PROTECTED] says... select count(*), category from posts group by category or select count(*) from posts group by category where category = 'foobar' should do the trick. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP-DB] Re: Query construction
select count(distinct(category)) as number_of_posts from posts; should do it. Steve Russ Michell [EMAIL PROTECTED] 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| | | -00-00 || | dateTo | date| | | -00-00 || | title| varchar(25) | | ||| | body | text| | | NULL || +--+-+--+-+++ 7 rows in set (0.00 sec) file://Table 'posts' in phpMyAdmin tab id category userdateFromdateTo titlebody 3 some stuffmaurice 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
