I have a system of Questions, Blogs, Answers, Tags, Followers and would like to create an SQL output with TAGS as the common field, showing counts of how many questions, blogs, answers, followers relating to that tag. An output would look like:
So far, I am able to create 4 different queries to count questions,answers,blogs,followers - with Date/Tag as the common fields.
Is there is a way to just use these queries and combine them/merge them/join them as they are? The queries themselves are all working individually. They are show below:
//QUESTIONS COUNT
SELECT date, tags.tag_name, count(questions.question_id) as question_count
FROM tags
LEFT JOIN tags_x
ON tags.tag_id = tags_x.tag_id
LEFT JOIN questions
ON tags_x.question_id = questions.question_id WHERE questions.type='question'
GROUP BY tags.tag_id ORDER BY tags.tag_name
//BLOG COUNT
SELECT date, tags.tag_name, count(questions.question_id) as blog_count
FROM tags
LEFT JOIN tags_x
ON tags.tag_id = tags_x.tag_id
LEFT JOIN questions
ON tags_x.question_id = questions.question_id WHERE questions.type='blog'
GROUP BY tags.tag_id ORDER BY tags.tag_name
//ANSWER COUNT
SELECT date,tags.tag_name, count(posts.question_id) as answer_count
FROM tags
LEFT JOIN tags_x
ON tags.tag_id = tags_x.tag_id
LEFT JOIN posts
ON tags_x.question_id = posts.question_id
GROUP BY tags.tag_id ORDER BY tags.tag_name
//FOLLOWERS COUNT
SELECT date, tags.tag_name, count(followers.unit_id) as followers_count
FROM tags
LEFT JOIN followers
ON tags.tag_id = followers.unit_id
WHERE followers.type='tag'
GROUP BY tags.tag_id ORDER BY tags.tag_name
There may be a way to create a complex join or set of sub-queries to do this, but I am not quite at that stage yet. I would appreciate these approaches as well.
Another question I have about these queries, do I need to include the common fields in the count(...) also in the GROUP BY to make it acceptable syntax? For example, in the last query, it works, but do I need to add the followers.unit_id like,
GROUP BY tags.tag_id, followers.unit_id
- which also works.
I have seen it done the other way (without common field) in many other Stack examples (without critique), but have been told it will be rejected with newer versions of MySQL. This is running on MySQL 5.6.2
EDIT - Added sample output from each query
EDIT: Since the question got closed, I'll post the answer here:
SELECT DATE_FORMAT(tags.date, "%d/%l/%Y") AS date, tags.tag_name,
IFNULL(C1.question_count,0) as questions,
IFNULL(C2.answer_count,0) as answers,
IFNULL(C3.blog_count,0) as blogs,
IFNULL(C4.followers_count,0) as followers FROM tags
LEFT JOIN
(SELECT date, tags.tag_name, count(questions.question_id) as question_count
FROM tags
LEFT JOIN tags_x
ON tags.tag_id = tags_x.tag_id
LEFT JOIN questions
ON tags_x.question_id = questions.question_id WHERE questions.type='question'
GROUP BY tags.tag_id ORDER BY tags.tag_name)
AS C1
ON tags.tag_name = C1.tag_name
LEFT JOIN
(SELECT date,tags.tag_name, count(posts.question_id) as answer_count
FROM tags
LEFT JOIN tags_x
ON tags.tag_id = tags_x.tag_id
LEFT JOIN posts
ON tags_x.question_id = posts.question_id
GROUP BY tags.tag_id ORDER BY tags.tag_name)
AS C2
ON tags.tag_name = C2.tag_name
LEFT JOIN
(SELECT date, tags.tag_name, count(questions.question_id) as blog_count
FROM tags
LEFT JOIN tags_x
ON tags.tag_id = tags_x.tag_id
LEFT JOIN questions
ON tags_x.question_id = questions.question_id WHERE questions.type='blog'
GROUP BY tags.tag_id ORDER BY tags.tag_name)
AS C3
ON tags.tag_name = C3.tag_name
LEFT JOIN
(SELECT date, tags.tag_name, count(followers.unit_id) as followers_count
FROM tags
LEFT JOIN followers
ON tags.tag_id = followers.unit_id
WHERE followers.type='tag'
GROUP BY tags.tag_id ORDER BY tags.tag_name)
AS C4
ON tags.tag_name = C4.tag_name
LIMIT 1000