#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171

    GROUP BY in LEFT OUTER JOIN table


    Hello;

    What is the corrcet approach? I wanna count the number of rows of the joined table (if any rows exist). But as it is aggregated function looks like I have to use GROUP BY the COUNTed row. The messes everything up. How can I achieve this?

    For example count the number of photos of each event.

    Code:
    SELECT event.id, COUNT(event_photos.photo) AS photos 
    FROM events LEFT OUTER JOIN event_photos ON events.id=event_photos.event_id
    GROUP BY photos
    Thanks
  2. #2
  3. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,675
    Rep Power
    171
    I came up with this but I am not very happy about it:
    Code:
    SELECT 
     Q_photos.C,
    `sincity_events`.`id` AS EID, 
    GROUP_CONCAT(sincity_events_celebrities.id) as CELEBS, 
    GROUP_CONCAT(sincity_celebrities.name) as CELEB_NAME
    
    FROM (`sincity_events`)
    
    LEFT JOIN `sincity_events_celebrities`
     ON `sincity_events_celebrities`.`event_id` = `sincity_events`.`id`
    
    LEFT JOIN `sincity_celebrities` ON `sincity_celebrities`.`id` = `sincity_events_celebrities`.`celebrity_id`
    
    LEFT OUTER JOIN 
    (SELECT event_id, COUNT(1) AS C FROM sincity_event_photos GROUP BY event_id) AS Q_photos
    ON Q_photos.event_id =  sincity_events.id
    
    GROUP BY `sincity_events`.`id`
    ORDER BY `date` ASC
    Please advise
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,243
    Rep Power
    4279
    Originally Posted by zxcvbnm
    For example count the number of photos of each event.
    change this --
    Code:
    SELECT event.id, COUNT(event_photos.photo) AS photos 
    FROM events LEFT OUTER JOIN event_photos ON events.id=event_photos.event_id
    GROUP BY photos
    to this --
    Code:
    SELECT event.id, COUNT(event_photos.photo) AS photos 
    FROM events LEFT OUTER JOIN event_photos ON events.id=event_photos.event_id
    GROUP BY event.id
    rule of thumb: your GROUP BY clause should always equal your SELECT clause without the aggregates
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,243
    Rep Power
    4279
    Originally Posted by zxcvbnm
    I came up with this but I am not very happy about it:
    neither am i

    you ditched the derived tables i coached you through in the last thread for what reason?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    As a professional teacher (not SQL), I've never yet met a pupil who I thought was beyond help, but I suppose such a hypothetical pupil must exist.

IMN logo majestic logo threadwatch logo seochat tools logo