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

Hi, and thanks for taking the time to reply



I used this (helped by your original query):


SELECT
  t3.c_year AS "Year",
  t3.c_month AS "Month",
  t3.il_mc AS  "Tumour count",
  t4.ill_nat AS "Type" FROM
(
  SELECT c_year, c_month, il_mc FROM
  (
    SELECT
    c_year,
    c_month,
    MAX(month_count) AS il_mc
  FROM
    (
      SELECT nature_of_illness as illness,
        EXTRACT(YEAR  FROM created_at) AS c_year,
        EXTRACT(MONTH FROM created_at) AS c_month,
        COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
      FROM illness
      GROUP BY illness, c_year, c_month
      ORDER BY c_year, c_month
    ) AS t1
  GROUP BY c_year, c_month
  ) AS t2
) AS t3
JOIN
(
SELECT
  EXTRACT(YEAR FROM created_at) AS t_year,
  EXTRACT(MONTH FROM created_at) AS t_month,
  nature_of_illness AS ill_nat,
  COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
ON t3.c_year = t4.t_year
AND t3.c_month = t4.t_month
AND t3.il_mc = t4.ill_cnt


and got this as a result:

SELECT
  t3.c_year AS "Year",
  t3.c_month AS "Month",
  t3.il_mc AS  "Tumour count",
  t4.ill_nat AS "Type" FROM
(
  SELECT c_year, c_month, il_mc FROM
  (
    SELECT
    c_year,
    c_month,
    MAX(month_count) AS il_mc
  FROM
    (
      SELECT nature_of_illness as illness,
        EXTRACT(YEAR  FROM created_at) AS c_year,
        EXTRACT(MONTH FROM created_at) AS c_month,
        COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
      FROM illness
      GROUP BY illness, c_year, c_month
      ORDER BY c_year, c_month
    ) AS t1
  GROUP BY c_year, c_month
  ) AS t2
) AS t3
JOIN
(
SELECT
  EXTRACT(YEAR FROM created_at) AS t_year,
  EXTRACT(MONTH FROM created_at) AS t_month,
  nature_of_illness AS ill_nat,
  COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
ON t3.c_year = t4.t_year
AND t3.c_month = t4.t_month
AND t3.il_mc = t4.ill_cnt



> 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.


Not what I required - there's no point in having ties randomly returning.


> 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.


Can't use DENSE_RANK() - MySQL 5.6 doesn't support it :-(


Thanks again for your help!


Rgs,


Pól...



> David J.