CODEWITHSUNDEEP
mysql
HenchHacker
HenchHacker

Reputation: 1626

Count multiple tables

I have several mysql tables like this:

blogs

articles

posts

I want to count the total entries by a specific member. I currently have this (while only using 3 tables for this example, there is in fact about 10 - 20 tables all structured the same):

SELECT COUNT('member_id') FROM blogs WHERE member_id=3 LIMIT 1;
SELECT COUNT('member_id') FROM articles WHERE member_id=3 LIMIT 1;
SELECT COUNT('member_id') FROM posts WHERE member_id=3 LIMIT 1;

You see the repetition? Is there any way of condensing that down to 1 query for example (doubt this works):

SELECT COUNT(blogs.'member_id') as total_blogs, 
COUNT(articles.'member_id') as total_articles, 
COUNT(posts.'member_id') as total_posts  
FROM blogs,articles,posts WHERE member_id=3 LIMIT 1;

P.S. Tried searching stackoverflow and google but keep getting things about using COUNT(*) or using groups, etc...

Upvotes: 1

Views: 86

Answers (3)

Saic Siquot
Saic Siquot

Reputation: 6513

Just for the record, I add here a second solution that admit multiple selection of IDs in a single query

SELECT m.member_id, 
       COALESCE(blogs.total_blogs,0)       as total_blogs, 
       COALESCE(articles.total_articles,0) as total_articles, 
       COALESCE(posts.total_posts,0)       as total_posts
FROM   members  m     -- I guess this table exists 
LEFT JOIN (SELECT member_id, COUNT('member_id') as total_blogs    FROM blogs    GROUP BY member_id) as blogs     on m.member_id = blogs.member_id
LEFT JOIN (SELECT member_id, COUNT('member_id') as total_articles FROM articles GROUP BY member_id) as articles  on m.member_id = articles.member_id
LEFT JOIN (SELECT member_id, COUNT('member_id') as total_posts    FROM posts    GROUP BY member_id) as posts     on m.member_id = posts.member_id
where m.member_id in (3,4,5)

fiddle here

Upvotes: 0

user1726343
user1726343

Reputation: 

SELECT COUNT(*) FROM(
    SELECT member_id FROM blogs
    UNION ALL
    SELECT member_id FROM articles
    UNION ALL
    SELECT member_id FROM posts
) AS activity
WHERE member_id=3
GROUP BY member_id

Sqlfiddle demonstration: http://sqlfiddle.com/#!2/366bd/2

Upvotes: 0

Saic Siquot
Saic Siquot

Reputation: 6513

This Works,

SELECT  
(SELECT COUNT('member_id') FROM blogs WHERE member_id=3) as total_blogs,
(SELECT COUNT('member_id') FROM articles WHERE member_id=3) as total_articles,
(SELECT COUNT('member_id') FROM posts WHERE member_id=3) as total_posts

and gives you all info in only one record

Upvotes: 1

Related Questions