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;
04-26-2006, 12:24 AM
I would rewrite the query as follows:
sum(followup.tpoint) as counts
cust inner join followup
ON cust.id = followup.id
and regdate < curdate()
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?