I think you want this or something similar: select count(distinct fld2) from yourtable where fld4 = 'am';
N.B. I have not tested this solution. Rhino ----- Original Message ----- From: "Ed Reed" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Friday, September 09, 2005 6:08 PM Subject: Simple Count Query I know this has to be a simple query but its really kickin' my butt. I have the table below where fld1 is the year, fld2 is a number, fld 3 is a subnumber, and fld4 is the user. I need to know the count of all the records for user am without the sub number getting in the way. For example, the first record for user am shows in the 3rd year number 1 with two sub records was for user am . That needs to be counted as one item. So when all the items are counted I should have a total of 5 items for user am and not 17 like you'd normally get Any thoughts? thanks +------+------+------+------+ | fld1 | fld2 | fld3 | fld4 | +------+------+------+------+ | 3 | 1 | a | am | | 3 | 1 | b | am | | 3 | 2 | a | am | | 3 | 3 | a | pm | | 3 | 3 | b | pm | | 3 | 3 | c | pm | | 4 | 1 | a | pm | | 4 | 2 | a | pm | | 4 | 3 | a | am | | 4 | 3 | b | am | | 4 | 3 | c | am | | 4 | 3 | d | am | | 4 | 3 | e | am | | 4 | 3 | f | am | | 4 | 4 | a | am | | 4 | 4 | b | am | | 5 | 1 | a | pm | | 5 | 1 | b | pm | | 5 | 1 | c | pm | | 5 | 1 | d | pm | | 5 | 1 | e | pm | | 5 | 2 | a | am | | 5 | 2 | b | am | | 5 | 2 | c | am | | 5 | 2 | d | am | | 5 | 2 | e | am | | 5 | 2 | f | am | +------+------+------+------+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
