0

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: enter image description here

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

//Questions Count: enter image description here

//Blog Count: enter image description here

//Answer Count: enter image description here

//Followers Count: enter image description here

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

The resulting output is: enter image description here

HDer
  • 385
  • 5
  • 17
  • 2
    Sample data and desired results would really help. The layout of your tables is not obvious. Why would `questions` have a column called `type` whose value is `'question'`? – Gordon Linoff May 21 '20 at 20:04
  • I guess that `type` column have different meaning even if its in 'questions' table. this table is saving questions and blogs as well because he is doing this one: `questions.type='blog'` – Ashu_90 May 21 '20 at 20:19
  • @Gordon - I have added some sample output. Yes, correct. The questions table contains both questions and blogs, as the only difference between them is the questions type can have answers in the posts table, and the blogs type does not have any answers. – HDer May 21 '20 at 20:33
  • They closed the question citing another answer, however, my question is different in that it queries multiple different tables rather than just one table - which is much simpler. I have found a solution which is neat and tidy, but unfortunately I cannot post it here. If somehow this gets opened up again, I can post the answer, as I'm sure it would help someone else. – HDer May 21 '20 at 21:20
  • If anyone should read this far, I have added the my answer to the bottom of the question, since this post has been closed – HDer May 21 '20 at 22:38

0 Answers0