Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: LEFT JOIN with GROUP BY

    Hi everybody,
    i don't understand what's wrong in my SQL sintax.

    QUERY:
    SELECT gid.iddevice,avg(gid.pac) as power, gi.`nominal power` FROM `general inputs data` as gid where idpark = 'PARCO00013' and idcustomer = 'PELLONI001' and datetime >= (
    '2012-04-02 09:45:00') group by iddevice LEFT JOIN `general inputs` as gi ON gid.idcustomer = gi.idcustomer and gid.idpark = gi.idpark

    Table GID :
    CUSTOMER PARK IDDEVICE PAC

    Table GI :
    CUSTOMER PARK IDDEVICE NOMINAL_POWER

    i want to take from GID the AVG(power) of last periodo for each DEVICE
    and with the JOIN i take the nominal power of DEVICES

    Thanks,
    Bye

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by svedese View Post
    i don't understand what's wrong in my SQL sintax.
    the WHERE clause must come ~after~ the FROM clause, which includes all joins

    then the GROUP BY clause must come after the WHERE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    2
    you're right...
    Thanks.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that your ON conditions of LEFT JOIN were incomplete.
    Code:
     LEFT  JOIN
           `general inputs` as gi
      ON   gid.idcustomer = gi.idcustomer
       and gid.idpark     = gi.idpark
    If iddevice was unique in all devices,
    "ON gid.iddevice = gi.iddevice" would be sufficient.

    If iddevice was unique in a idcustomer and idpark,
    it might be necessary to add "gid.iddevice = gi.iddevice" in the ON conditions, like
    Code:
     LEFT  JOIN
           `general inputs` as gi
      ON   gid.idcustomer = gi.idcustomer
       AND gid.idpark     = gi.idpark
       AND gid.iddevice   = gi.iddevice
    Last edited by tonkuma; 04-02-12 at 13:16. Reason: Rewrite whole text.

Posting Permissions

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