Postgres: count value from column which has highest count

Question

I have a table as below

IDnumber     ProcessID
  1            21
  2            22
  3            21
  4            21
  5            22
  6            22
  7            22
  8            22
  9            23

Now I have a query as below

select count(*) filter (where ProcessID IN (21, 22)) FROM table

This query will give output = 8 which is correct.

However, I need a query to find the count of only that column which is highest between the provided list of columns in where condition.

In the above example '22' occurs 5 times hence it is higher than '21' which occurs 3 times.

Is there any function/logic to do this?

Thanks

Answer 1

you can try like

 with cte as
 (select count(*) as cnt,ProcessID
 FROM table where ProcessID IN (21, 22)
 group by ProcessID
 ) select max(cnt) from cte

Answer 2

If I understand correctly, you can use aggregation and limit:

select processid, count(*)
from t
where ProcessID in (21, 22)
group by processid
order by count(*)
limit 1;

Note that this returns one row, even when there are ties. If you want all the rows with ties, you can use window functions:

select p.*
from (select processid, count(*) as cnt,
             rank() over (order by count(*) desc) as seqnum
      from t
      where ProcessID in (21, 22)
      group by processid
     ) p
where seqnum = 1;

You could also combine this on row:

select array_agg(processid), cnt
from (select processid, count(*) as cnt
      from t
      where ProcessID in (21, 22)
      group by processid
     ) p
group by cnt
order by cnt desc
limit 1

Answer 3

First u need to group by the provided list and than used that as the table

Select a.ProcessID,MAX(a.Cnt) 
from (
Select ProcessID,count(*) as Cnt  
from table where ProcessID IN (21,22) 
group by ProcessID order by Count(*) DESC) a

Answer 4

You can use MAX() window function:

SELECT DISTINCT MAX(COUNT(*)) OVER()
FROM tablename
WHERE ProcessID IN (21, 22)
GROUP BY ProcessID

See the demo.