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

On Mon, Apr 30, 2018 at 7:25 AM, Paul Linehan <linehanp(at)tcd(dot)ie> wrote:

> Now, what I want is to find which illness was reported most in a given
> month and in a given year.
>

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.