Select 2 tables with Aggregate functions

[cyberplasma]

Hello,

I have two tables (simplified for this question):

Categories:

ID
name

Books:

ID
catID
name

I would like to show the number of books in each category, using just one mySQL recordset:

• Fiction (78)
• Non-Fiction (192)
• Computer (86)

Does someone has a solution for this? I see that http://php.resourceindex.com uses a similar system.

Thanks
Bjorn

[the_Igel]

select count(*), categories.name from categories, books group by categories.id
, isn't it?

[cyberplasma]

Thanks for the effort anyway, but unfortunately it is not that simple. Someone told me that I would need a subselect but that is not supported with mySQL. There must be a workaround somehow, as I've seen several PHP-driven websites that have this feature.

Does anyone know the solution to this?

Thanks,
Bjorn

[the_Igel]

oh yes, i missed WHERE clause, sorry.

SELECT count(*), categories.name FROM categories, books
WHERE categories.id = books.catid
GROUP BY categories.id

This query works OK. I've tried it.

[cyberplasma]

I have tried this before and was unsuccesful.

1. How does the SQL statement know on which table to perform the count (ambiguous tables)

2. I need to add a fieldname for the countfield 'AS count' or something like that

3. The biggest issue is that this statement won't perform because the group by clause is set on categories.id, but the field list includes a non-groupby field: categories.name where it blocks.

I have tried this path before, I think I need a totally different sql-statement with an 'IN' clause or some kind of subselect workaround. Like I said, it is not that simple.

Has anyone worked this kind of problem out before, and put it online to see? I'm sure it's a common construct as I need it for several 'web-tasks'.

[the_Igel]

1. How does the SQL statement know on which table to perform the count (ambiguous tables)

It performs count not on tables, but on join categories, books. So nothing ambiguous.

3. The biggest issue is that this statement won't perform because the group by clause is set on categories.id, but the field list includes a non-groupby field: categories.name where it blocks.

I don't see why categories.name should block any grouping. You can even make grouping by name, not ID, if you want.

Anyway, this arguing is rather pointless: this query works in my system and doesn't work in yours. So it should be version or maybe configuration issue.

[[deleted]]

This is a straight join, so just elaborating the GROUP BY should do the trick:

SELECT count(1), categories.name
FROM categories, books
WHERE categories.id = books.catid
GROUP BY categories.id, categories.name

You could do this with a subselect, but that would be overkill because there is only one join, and the categories table is very small.

[cyberplasma]

Wonderful, it finally works!!! Thanks a lot for your help!!

Bjorn