Joining Tables and doing counts

[treeleaf20]

All,
I have three tables, pictures, story and users. I'd like to get the count of all the pictures for a person and all the stories by the same username:

I tried to do this:

Select users.user_name, MAX(pictures.date) as picture_date, COUNT(pictures.picture_id) as pictures, COUNT(story.story_id) as stories, MAX(story.date) as story_date
from users
Left Outer Join pictures
On users.user_name=pictures.user_name
Left Outer Join story
On users.user_name=story.user_name
Group by users.user_name
Order by pictures DESC


It's close but it doesn't do the proper count for stories and pictures and just makes them the same number. Please help.

[Old Pedant]

Will always get the same COUNT when coded like that, since you are *really* counting records in the ENTIRE JOINED result.

There's a very messy answer that works in all ANSI-compliant DBs.

But fortunately, MySQL has a nice handy extension that makes this trivial:

Select users.user_name,
MAX(pictures.date) as picture_date,
COUNT(DISTINCT pictures.picture_id) as pictures,
COUNT(DISTINCT story.story_id) as stories,
MAX(story.date) as story_date
from users
Left Outer Join pictures On users.user_name=pictures.user_name
Left Outer Join story On users.user_name=story.user_name
Group by users.user_name
Order by pictures DESC

[treeleaf20]

That worked great. Anyway can I actually SUM the pictures and stories and store this in a new column? I'd actually like to sort on this number instead of just the pictures.

Thanks.

[Old Pedant]

You could, but why?

Why not simply

Select users.user_name,
MAX(pictures.date) as picture_date,
COUNT(DISTINCT pictures.picture_id) as pictures,
COUNT(DISTINCT story.story_id) as stories,
MAX(story.date) as story_date
from users
Left Outer Join pictures On users.user_name=pictures.user_name
Left Outer Join story On users.user_name=story.user_name
Group by users.user_name
Order by ( pictures + stories ) DESC

[treeleaf20]

When I try and do this MySQL gives me an error. It says:

Reference 'pictures' not supported (reference to group function)

[Old Pedant]

Okay, not surprising. Access has the same limitation.

You can *probably* get around it, thus:

Select users.user_name,
MAX(pictures.date) as picture_date,
COUNT(DISTINCT pictures.picture_id) as pictures,
COUNT(DISTINCT story.story_id) as stories,
MAX(story.date) as story_date
from users
Left Outer Join pictures On users.user_name=pictures.user_name
Left Outer Join story On users.user_name=story.user_name
Group by users.user_name
Order by ( COUNT(DISTINCT pictures.picture_id) + COUNT(DISTINCT story.story_id) ) DESC;

Don't worry about using the COUNT twice; the MySQL engine is smart enough to optimize that into a realization that it can reuse the prior count.

*IF* that doesn't work, then worst come to worst you could do:

SELECT x.* FROM
(Select users.user_name,
MAX(pictures.date) as picture_date,
COUNT(DISTINCT pictures.picture_id) as pictures,
COUNT(DISTINCT story.story_id) as stories,
MAX(story.date) as story_date
from users
Left Outer Join pictures On users.user_name=pictures.user_name
Left Outer Join story On users.user_name=story.user_name
Group by users.user_name
) AS x
ORDER BY (x.pictures + x.stories) DESC;