Oops, that should be: select count(distinct fld1, fld2) from yourtable where fld4 = 'am';
That version of the query is tested and works with your data; it returns the fact that there are 5 combinations of fld1 and fld2 for all the rows where fld4 is 'am'. Rhino ----- Original Message ----- From: "Rhino" <[EMAIL PROTECTED]> To: "Ed Reed" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com> Sent: Friday, September 09, 2005 6:26 PM Subject: Re: Simple Count Query > 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] > > > -- > 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]
