A full set of aggregate functions is now available to the API in Spring '10 which allow you to run queries similar to reports programmatically. The set includes “GROUP BY” query expressions, such as SUM, MIN, MAX, AVG, HAVING, and full support for grouping by day, month, year, etc. Sample queries you can now execute via the API include “rank users by how many opportunities they own” or “show sum of created opportunity amount by week”.

For example, you can use GROUP BY to determine how many leads are associated with each LeadSource value:

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource

Here are a few more examples drawn from the release notes.

Returns the number of rows matching the query criteria:

SELECT COUNT() FROM Account WHERE Name LIKE 'a%'

Returns the minimum value of a field:

SELECT MIN(CreatedDate), FirstName, LastName FROM Contact GROUP BY FirstName, LastName

Returns the total sum of a numeric field:

SELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60

You can also use date functions to group or filter your data by various date periods. For example, you could use the CALENDAR_YEAR() function to find the sum of the Amount values for all your opportunities for each calendar year:

SELECT CALENDAR_YEAR(CreatedDate), SUM(Amount) FROM Opportunity GROUP BY CALENDAR_YEAR(CreatedDate)