Count and group by

Question

I have two tables :

exp_channel_titles T (parent table)
exp_category_posts P (many to many table that links to T via column entry_id)

I need to know how many entries per category each author_id has in P using the author_id, entry_id fields from T where T.channel_id = 7.

e.g.

SELECT cat_id, author_id 
FROM exp_channel_titles t, exp_category_posts p
WHERE t.channel_id = 7
AND t.entry_id = p.entry_id
GROUP BY p.cat_id
GROUP BY t.author_id

Answer 1

Try this:

SELECT 
    p.cat_id, 
    t.author_id,
    count(*) as cat_entries
FROM 
    exp_channel_titles t
    join exp_category_posts p on t.entry_id = p.entry_id
WHERE
    t.channel_id = 7
GROUP BY
    p.cat_id,
    t.author_id