Determine which year-month has the highest and lowest value

Question

Here's my first query to shows the number of customers added per year-month

select count(name) AS CUSTOMER, 
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon
from x
group by extract(year from create_date), 
         extract(month from create_date)
order by yr desc, mon desc;

CUSTOMER	YR	MON
3	2019	07
4	2015	02
100	2014	09
3	2014	04

I tried the query

SELECT MAX(count(*))
FROM x
GROUP BY create_date;

in the results I have;

MAX(COUNT(*))
100

need to see the year and month in the result.

How to do this?

Your question has several format problems, but, ignoring that, have you tried parsing to number the year and month of the date? `to_number(to_char(create_date, 'YYYYMM'))`. — Nakarukatoshi Uzumaki, Sep 17 '21 at 08:21

Littlefoot · Answer 1 · 2021-09-17T08:43:53.490

1

The way I understood the question, you'd use rank analytic function in a subquery (or a CTE) and fetch rows whose count is either minimum or maximum. Something like this:

with temp as
  (select to_char(create_date, 'yyyymm') yyyy_mm,
          count(*) cnt,
          --
          rank() over (order by count(*)  asc) rnk_min,
          rank() over (order by count(*) desc) rnk_max
   from x
   group by to_char(create_date, 'yyyymm')
  )
select yyyy_mm,
       cnt
from temp
where rnk_min = 1
   or rnk_max = 1;

edited Sep 17 '21 at 08:43

answered Sep 17 '21 at 08:23

Littlefoot

131,892
15
35
57

"cnt" : invalid indentifier – Jeanne Sep 17 '21 at 08:42
Not any more; forgot to set alias (line #3). Fixed; sorry. – Littlefoot Sep 17 '21 at 08:44

score 0 · Answer 2 · answered Sep 17 '21 at 12:25

You can use two levels of aggregation and put the results all in one row using keep (which implements a "first" aggregation function):

select max(num_customers) as max_num_customers,
       max(yyyymm) keep (dense_rank first order by num_customers desc) as max_yyyymm,
       min(num_customers) as max_num_customers,
       max(yyyymm) keep (dense_rank first order by num_customers asc) as in_yyyymm,
from (select to_char(create_date, 'YYYY-MM') as yyyymm,
             count(*) AS num_customers
      from x
      group by to_char(create_date, 'YYYY-MM'
     ) ym

score 0 · Answer 3 · answered Sep 17 '21 at 12:55

From Oracle 12, you can use FETCH FIRST ROW ONLY to get the row with the highest number of customers (and, in the case of ties, the latest date):

SELECT count(name) AS CUSTOMER,
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon
FROM   x
GROUP BY
       extract(year from create_date), 
       extract(month from create_date)
ORDER BY
       customer DESC,
       yr DESC,
       mon DESC
FETCH FIRST ROW ONLY;

If you want to include ties for the highest number of customers then:

SELECT count(name) AS CUSTOMER,
       extract(year from create_date) as yr, 
       extract(month from create_date) as mon
FROM   x
GROUP BY
       extract(year from create_date), 
       extract(month from create_date)
ORDER BY
       customer DESC
FETCH FIRST ROW WITH TIES;

Determine which year-month has the highest and lowest value

3 Answers3