-1

I have two tables in my database: tb_authors and tb_posts.

I need to display a list of authors ordered by the number of posts each one has in descending order.

Both tables have in common an id_author key.

Anyone can help me?

Ok, so far I got this:

SELECT tb_authors.*, COUNT(tb_posts.*) AS thecount FROM tb_posts, tb_authors WHERE tb_authors.id_author = tb_posts.id_author ORDER BY thecount DESC;

But it's not working. It returns 1 column with one Author and the thecount has the value of total posts.

tumultous_rooster
  • 12,150
  • 32
  • 92
  • 149
fackz
  • 531
  • 2
  • 6
  • 12

1 Answers1

1

Oddly enough, the table names and common key are enough to answer this one.

SELECT tb_authors.*, COUNT(tb_posts.*) AS thecount WHERE tb_posts.id_author = tb_authors.id_author ORDER BY thecount DESC
Andrew Coder
  • 1,058
  • 7
  • 13
  • Hey, thanks for your help! It returns an syntax error near '*) AS thecount – fackz Jan 28 '16 at 23:32
  • Hmm, I'm missing something(this isn't a query I do often either). Try reading this post: http://stackoverflow.com/questions/606234/select-count-from-multiple-tables – Andrew Coder Jan 28 '16 at 23:34