SUM() and MAX() at the same time

I'd like to know how can I solve the following:
I have to group a number of specific tuples together to get the sum and then retrieve the maximum of that sum. I know I might have to use the SQL command GROUP BY, and I got the sums, but I did not know how to retrieve the maximum in the same query.

You can use any number of different aggregate functions in a query. They will all be calculated for each group. Consider the following example --

myTable
ID  Category  Rating
 1    foo       7
 2    bar       2
 3    foo       5
 4    foo       3
 5    bar       -
 6    bar       4

If you run the following query --

select Category
     , count(*) as Records
     , count(Rating) as Ratings
     , sum(Rating) as S
     , max(Rating) as M
     , avg(Rating) as A
  from myTable
group by Category

... then you will get these results --

Category  Records  Ratings  S   M   A
  foo       3        3     15   7   5       
  bar       3        2      6   4   3

Notice that all aggregate functions except COUNT(*) ignore the NULL Rating for the ID=5 row. COUNT(*) counts rows, whereas COUNT(col) counts non-null values.

So to answer your question, just go ahead and use SUM() and MAX() in the same query.

For More Information

• What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.