GROUP BY in LEFT OUTER JOIN table

Dev Shed Forums Sponsor:

January 28th, 2013, 08:51 PM

zxcvbnm

A Change of Season

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2004

Posts: 1,666

Time spent in forums: 2 Weeks 5 Days 15 h 23 m 16 sec
Reputation Power: 71

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

January 28th, 2013, 09:06 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,666

Time spent in forums: 2 Weeks 5 Days 15 h 23 m 16 sec
Reputation Power: 71

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

January 29th, 2013, 05:02 AM

r937

SQL Consultant

Dev Shed God 43rd Plane (26000 - 26499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

January 29th, 2013, 05:03 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,438

Time spent in forums: 3 Months 1 Week 2 Days 13 h 52 m 18 sec
Reputation Power: 4141

Quote:

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?

January 29th, 2013, 07:26 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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.

GROUP BY in LEFT OUTER JOIN table

Developer Shed Advertisers and Affiliates