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

Gordon Linoff · Accepted Answer · 2020-07-18 11:39:19Z

4

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

edited Jul 18, 2020 at 11:39

answered Jul 18, 2020 at 11:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2020-07-18 09:07:23Z

0

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

answered Jul 18, 2020 at 9:07

Zaynul Abadin Tuhin

32.1k6 gold badges38 silver badges67 bronze badges

Comments

Gagantous · Accepted Answer · 2020-07-18 09:16:52Z

0

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

answered Jul 18, 2020 at 9:16

Gagantous

5388 gold badges31 silver badges72 bronze badges

Comments

forpas · Accepted Answer · 2020-07-18 22:12:45Z

0

You can use MAX() window function:

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

See the demo.

answered Jul 18, 2020 at 22:12

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

Postgres: count value from column which has highest count

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related