MySQL - select maximum sum

Question

I have a MySQL table as below.

**AuthorID**, **PublicationName**, ReferenceCount, CitationCount

AuthorID and PublicationName act as the primary key. I need to find the maximum sum of ReferenceCount and CitationCount for all the authors. For example, the data is as below.

1 AAA 2 5
1 BBB 1 3
1 CCC 2 4
2 AAA 1 4

In this case, I need my output as,

1 AAA 7
2 AAA 5

I tried the below query.

SELECT AuthorID, PublicationName, Max(Sum(ReferenceCount + CitationCount)) 
from Author 
Group by AuthorID, PublicationName

If I use max(sum(ReferenceCount + CitationCount)) group by AuthorID, PublicationName I get an error as "Invalid use of Group function". I believe I should include Having clause in my query. But am not sure on how to do the same.

By the looks of your output, that just looks like the sum to me, and put in order of that sum. — christopher
– christopher, Commented Sep 4, 2013 at 15:16
The MySQL naming convention discourages CamelCase table and field names, instead an underscore should be used to separate words (eg. author_id and publication_name). — Jeffrey
– Jeffrey, Commented Sep 4, 2013 at 15:19

Gordon Linoff · Accepted Answer · 2013-09-04 15:22:56Z

2

If I understand the question right, you want all the records for the publication that has the most citations. The publication and their citation counts is given by:

SELECT PublicationName, Sum(ReferenceCount + CitationCount)
from Author
Group by PublicationName
order by Sum(ReferenceCount + CitationCount) desc
limit 1;

The order by and limit 1 give you the highest value.

If you want all records for the publication with the maximum sum:

select a.*
from Author a join
     (SELECT PublicationName, Sum(ReferenceCount + CitationCount)
      from Author
      Group by PublicationName 
      order by Sum(ReferenceCount + CitationCount) desc
      limit 1
     ) asum
     on a.PublicationName = asum.PublicationName

edited Sep 4, 2013 at 15:22

answered Sep 4, 2013 at 15:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ramesh Over a year ago

Thanks for your prompt reply. I have 6434 rows in my table. If I run distinct AuthorID query, I get 389 rows. However, if I use the query as you suggested, I am getting the output for just one author ID.

Gordon Linoff Over a year ago

@Ramesh. . . That usually happens when you leave out a group by clause. However, I modified the answer, because I think I initially misunderstood it.

Ramesh Over a year ago

I still see output for only one row. I am not sure why it's giving output for only one author ID as I have 389 distinct author ID's in my table.

Minoru · Accepted Answer · 2013-09-04 15:39:38Z

0

Try this:

SELECT AuthorID, PublicationName, Max(ReferenceCount+CitationCount) 
FROM Author 
GROUP BY AuthorID

The problem with your query was that the SUM() sums a column's value for many rows. It cannot be used to sum columns the way you wanted. For that, just use the plus (+), normally.

answered Sep 4, 2013 at 15:39

Minoru

1,7303 gold badges21 silver badges45 bronze badges

Collectives™ on Stack Overflow

MySQL - select maximum sum

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related