Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    mwm
    mwm is offline
    Regular Coder
    Join Date
    Oct 2005
    Posts
    174
    Thanks
    0
    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;

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I would rewrite the query as follows:

    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' ;
    (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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •