MYSQL - Using SUM

Question

I am trying to find the top 10 authors who has written the most number of books.

I have two table as follows:

Author table:

author_name
publisher_key

Publication table:

publisher_id
publisher_key
title
year
pageno

To find the result, I tried using the following query:

SELECT a.author_name, SUM(p.pageno)
FROM author a JOIN publication p ON a.publisher_key = p.publisher_key
GROUP BY a.author_name
LIMIT 10;

I have no idea why when I run this query it takes ages though the number of records is only 200.

So is your question about why the query is taking so long? How many records do the `authored` and `publication` tables have? — Tim Biegeleisen, Oct 14 '15 at 04:59
I have 200 records in publication and for the authored table there is only 20 records. It seems to be going into an infinite loop. My question is that I cant seem to get a result from the above query. — windboy, Oct 14 '15 at 05:06

score 1 · Answer 1 · answered Oct 14 '15 at 05:01

1

Try

SELECT a.author_name, count(*)
  FROM author a 
       INNER JOIN publication p ON a.publisher_key = p.publisher_key
  GROUP BY a.author_name 
  ORDER BY 2 desc
  LIMIT 10;

You want to know who write most number of books, so you need to count the number of registries by author.

The order by 2 desc will order your query from the bigger number to the lesser 2 means the second field on the select list.

answered Oct 14 '15 at 05:01

Jorge Campos

22,647
7
56
87

Hi i tried the sql query but it still's go into an infinite loop. I am just curious what does this do "GROUP BY 2 desc" ? – windboy Oct 14 '15 at 05:07
@windboy It was a typo, I already corrected it. `ORDER BY 2 desc` there is also an explanation for it – Jorge Campos Oct 14 '15 at 06:01

score 0 · Accepted Answer · edited May 23 '17 at 10:28

If it is as ydoow suggested that it maybe a locking issue, try running your select with NOLOCK to confirm.

SELECT a.author_name, count(*)
  FROM author a WITH (nolock)
       INNER JOIN publication p WITH (nolock) ON a.publisher_key = p.publisher_key
  GROUP BY a.author_name 
  ORDER BY 2 desc
  LIMIT 10;

You can get more info here: Any way to select without causing locking in MySQL?

MYSQL - Using SUM

2 Answers2