Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Simple query has got me
04-25-2006, 11:30 PM #1
- Join Date
- Oct 2005
- Thanked 0 Times in 0 Posts
Simple query has got me
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, 01:24 AM #2
- Join Date
- Mar 2006
- St. Catharines, Ontario Canada
- Thanked 148 Times in 139 Posts
I would rewrite the query as follows:
(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.Code: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' ;
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?