Re: a mysql query stuck for the last week ......

thnx a lot per but that aint the problem :( i am running about 25 similar queries ..... like select count(distinct village_reg.pk_id) and they all work :( except for this one theres sumthin the matter with this goddammed function pal i hope some can resolve this as i cant :( thnx for ur response again all :S toby .... --- Per Lundberg <per@nobolt.com> wrote: > On Wed, 2003-05-21 at 14:37, toby z wrote: > > if the above funct is right this query otta work:: > > > > > > SELECT count(distinct tbl_chld_reg_400.chld_id) > > from tbl_chld_reg_400 > > where month(tbl_chld_reg_400.dom) = $loop_mon > > and year(tbl_chld_reg_400.dom) = $yr > > and ((DATE_FORMAT('tbl_chld_reg_400.dom', '%Y') - > > DATE_FORMAT('tbl_chld_reg_400.dob', '%Y') > > - (DATE_FORMAT('tbl_chld_reg_400.dom', '00-%m-%d') < > > DATE_FORMAT('tbl_chld_reg_400.dob', '00-%m-%d'))) >=1) > > > > and ((DATE_FORMAT('tbl_chld_reg_400.dom', '%Y') - > > DATE_FORMAT('tbl_chld_reg_400.dob', '%Y') > > - (DATE_FORMAT('tbl_chld_reg_400.dom', '00-%m-%d') < > > DATE_FORMAT('tbl_chld_reg_400.dob', '00-%m-%d'))) <=5) > > and tbl_chld_reg_400.gndr='1' > > No... It won't. Take a look at this query for a short while: > SELECT > count(distinct tbl_chld_reg_400.chld_id) FROM tbl_child_reg_400 > > If you analyze the different parts of this query carefully, I think > you > will realize what the problem is. The thing is: both COUNT and > DISTINCT > are SQL functions, and functions *always* (just as in PHP) need to > have > () -- parantheses. > > So if you rewrite this very basic query to SELECT > COUNT(DISTINCT(tbl_chld_reg_400.chld_id)) FROM tbl_chld_reg_400, > you > should get all unique child IDs. And then you can add your > conditional > as you wish. > > HTH. > > -- > Best regards, > > Per Lundberg / Capio ApS > Phone: +46-18-4186040 > Fax: +46-18-4186049 > Web: http://www.nobolt.com > > ATTACHMENT part 2 application/pgp-signature name=signature.asc __________________________________________________ It's Samaritans' Week. Help Samaritans help others. Call 08709 000032 to give or donate online now at http://www.samaritans.org/support/donations.shtm