CODEWITHSUNDEEP
mysqlgroup-bysum
Ward
Ward

Reputation: 3318

Ordering Based On Sum

I have three mysql tables: articles, categories and tags.

I want to get a list of all the categories and tags that are linked to the articles and have them sorted by the number of times each one occurs.

The articles table has the following columns: article_id and name

The categories and tags tables have the following columns: article_id and tid.

Ultimately I'm looking for the tids.

Here's an example of what the tables may look like:

ARTICLES
article_id   name 
1            Article 1
2            Article 2
3            Article 3
4            Article 4

CATEGORIES
article_id   tid
1            100
2            100
3            100
4            101

TAGS
article_id   tid
1            200
2            200
3            200
4            200
3            201
4            201

What I would like to see would be:

RESULTS
tid          count
200          4
100          3
201          2
101          1

because tid 200 is used 4 times, tid 100 is used 3 times, tid 201 is used 2 times and tid 101 is used 1 times.

And ideas?

Upvotes: 0

Views: 96

Answers (1)

Kermit
Kermit

Reputation: 34054

This will work assuming that you'll never have the same tid in categories and tags

SELECT a.tid, a.count
FROM
  (SELECT tid, COUNT(article_id) AS count
  FROM categories
  GROUP BY tid
  UNION ALL
  SELECT tid, COUNT(article_id) AS count
  FROM tags
  GROUP BY tid) a
ORDER BY a.count DESC

See the demo

If you may have duplicates, use this:

SELECT a.tid, COUNT(article_id) AS count
FROM
  (SELECT tid, article_id 
  FROM categories
  UNION ALL
  SELECT tid, article_id
  FROM tags) a
GROUP BY a.tid
ORDER BY COUNT(article_id) DESC

See the demo

Result

| TID | COUNT |
---------------
| 200 |     4 |
| 100 |     3 |
| 201 |     2 |
| 101 |     1 |

Upvotes: 2

Related Questions