Re: SUM and MAX over different periods - months over several years?

Basically, you want to computed an ordered listing of all illnesses counts grouped by year-month, then return the first one of them (in the case of ties are you expecting to return two records or one)?

SELECT DISTINCT ON (year, month) year, month, count_of_illness, illness

FROM (

SELECT year, month, count(*) AS count_of_illness, illness

FROM ...

GROUP BY year, month

ORDER BY 1, 2, 3 DESC

) grp

This will return exactly one record, "the first" for each year/month combination in your data.  First is determined by the sort in the subquery.

If you need to return multiple records in the case of ties you either, more of less, self-join on (year, month, count) or use something like dense_rank() OVER (partition by year, month order by count_of_illness desc) to assign a rank of 1 to all highest count items and then add a "where dense_rank = 1" filter to the query.

David J.