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.