Q

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 editor@searchDatabase.com 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.

This was first published in April 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close