...

View Full Version : Simple query has got me



mwm
04-25-2006, 11:30 PM
Can't seem to get this query to work. I need to pull from two tables where the regdate is less than the current date and where the sum of table 2 column tpcount is less than 2. Any Ideas


select cust.name, cust.regdate, sum(followup.tpoint) as counts from cust,followup where cust.id = followup.id and regdate < curdate() and tpcount < '2' group by name;

guelphdad
04-26-2006, 01:24 AM
I would rewrite the query as follows:



select cust.name,
cust.regdate,
sum(followup.tpoint) as counts
from
cust inner join followup
ON cust.id = followup.id
and regdate < curdate()
group by
name,
regdate
having tpcount < '2' ;


(note the ON clause is just clearer than using the list of tables with the where clause. In fact you can run into an error using mysql5 when using list join syntax.

also note that every column named in your select part of the query should also appear in the group by clause. mysql lets you leave them out but warns you of unpredictible data. See the manual for GROUP BY HIDDEN FIELDS for more information on that.

Lastly, note that you refer to the sum of tpcount in your question but you don't refer to that column as a sum anywhere in your select query. can you clarify if you have mixed that column and tpoint column up?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum