How to select max of count in PostgreSQL

I have table in PostgreSQL with the following schema:

Category    |   Type 
------------+---------
A           |   0 
C           |   11 
B           |   5 
D           |   1 
D           |   0 
F           |   2   
E           |   11 
E           |   9 
.           |   . 
.           |   .

How can I select category wise maximum occurrence of type? The following give me all:

SELECT 
    category, 
    type, 
    COUNT(*)
FROM 
    table
GROUP BY 
    category, 
    type
ORDER BY 
    category, 
    count 
    DESC

My expected result is something like this:

Cat |Type |Count --------+-------+------

A |0 |5

B |5 |30

C |2 |20

D |3 |10

That is the type with max occurrence in each category with count of that type.

You can use the following query:

SELECT category, type, cnt
FROM (
   SELECT category, type, cnt,
          RANK() OVER (PARTITION BY category 
                       ORDER BY cnt DESC) AS rn
   FROM (
      SELECT category, type, COUNT(type) AS cnt
      FROM mytable
      GROUP BY category, type ) t
) s
WHERE s.rn = 1

The above query uses your own query as posted in the OP and applies RANK() windowed function to it. Using RANK() we can specify all records coming from the initial query having the greatest COUNT(type) value.

Note: If there are more than one types having the maximum number of occurrences for a specific category, then all of them will be returned by the above query, as a consequence of using RANK.

Demo here

PostgreSQL MAX Function: Get Maximum Value in a Set, PostgreSQL MAX function in subquery SELECT * FROM payment WHERE amount = ( SELECT MAX (amount) FROM payment ); First, the subquery uses the MAX() function to return the highest payment and then the outer query selects all rows whose amounts are equal the highest payment returned from the subquery. PostgreSQL MAX function with GROUP BY clause. You can combine the MAX function with the GROUP BY clause to get the maximum value for each group. For example, the following query gets the highest payment paid by each customer. SELECT customer_id, MAX (amount) FROM payment GROUP BY customer_id;

If I understand correctly, you can use window functions:

SELECT category, type, cnt
FROM (SELECT category, type, COUNT(*) as cnt,
             ROW_NUMBER() OVER (PARTITION BY type ORDER BY COUNT(*) DESC) as seqnum
      FROM table
      GROUP BY category, type
     ) ct
WHERE seqnum = 1;

SQL MAX count() and having, In this page we are discussing the usage of SQL COUNT() along with the SQL MAX(). NET Database SQL(2003 standard of ANSI) MySQL PostgreSQL SQLite SELECT MAX (mycount) FROM (SELECT agent_code� testdb=# SELECT MAX(salary) FROM COMPANY; The above given PostgreSQL statement will produce the following result − max ------- 85000 (1 row) You can find all the records with maximum value for each name using the GROUP BY clause as follows −

SELECT 
    category, 
    type, 
    COUNT(*)
FROM 
    table
GROUP BY 
    category, 
    type
HAVING 
    COUNT(*) = (SELECT MAX(C) FROM (SELECT COUNT(*) AS C FROM A GROUP BY A) AS Q)

EDITED: I apologize to readers,

COUNT(*) = (SELECT MAX(COUNT(*)) FROM table GROUP BY category,type) 

is the ORACLE version, postgresql version is:

COUNT(*) = (SELECT MAX(C) FROM (SELECT COUNT(*) AS C FROM A GROUP BY A) AS Q)

Documentation: 8.2: Aggregate Functions, compute the count , sum , avg (average), max (maximum) and min (minimum) over SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG. SELECT COUNT (*) FROM table_name WHERE condition; When you apply the COUNT (*) function to the entire table, PostgreSQL has to scan the whole table sequentially. If you use the COUNT (*) function on a big table, the query will be slow. This is related to the PostgreSQL MVCC implementation.

SELECT category , MAX (Occurence) FROM (SELECT t.category as category , Count(*) AS Occurence FROM table t);

Re: How to find the max value in a select?, COUNT 1 > > In other words, I want to find the one row matched by my WHERE clause > which is the maximum or minimum of all the rows that matched it. I PostgreSQL MAX as a level. If we want to find the maximum salary from all employees and show the result against 'Maximum Salary' head in the employee table, the following SQL can be used. SQL. Code: SELECT MAX(salary) AS "Maximum Salary" FROM employee; OR. Code: SELECT MAX(DISTINCT salary) AS "Maximum Salary" FROM employee; OR. Code: SELECT MAX(ALL salary) AS "Maximum Salary" FROM employee; Output:

SELECT 
    category, 
    type, 
    COUNT(*) AS count
FROM 
    table
GROUP BY 
    category, 
    type
ORDER BY 
    category ASC

Documentation: 8.0: Aggregate Functions, Similarly, the aggregate functions max() and count() always require a sequential scan if applied to SELECT col FROM sometable ORDER BY col ASC LIMIT 1;. To get the maximum number of agents as column alias 'mycount' from the 'orders' table with the following condition -. 1. 'agent_code' should be in a group, the following SQL statement can be used : SELECT MAX ( mycount) FROM (SELECT agent_code,COUNT( agent_code) mycount FROM orders GROUP BY agent_code); Copy.

max count - PostgreSQL Database, i have the following query, i need to get the max count from a table select max( count(*)) from buys group by v_id bt i have the error aggregate� max count. PostgreSQL Database Forums on Bytes. thx a lot it worksbut can u help me some more plzzi need to get the corresponding v_id where the count is maximum..

How can I correctly choose maximum number of occurrences of a , This isn't trivial. First, you want group by id and name and count the rows: SELECT COUNT(*) GROUP BY id, name. Then select the� I know the pg_stat_activity view, like select count(*) from pg_stat_activity, but I think this method is Stack Exchange Network Stack Exchange network consists of 176 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

PostgreSQL: HAVING Clause, This PostgreSQL tutorial explains how to use the PostgreSQL HAVING clause with SELECT expression1, expression2, expression_n, aggregate_function It can be an aggregate function such as sum, count, min, max, or avg functions. SELECT count(*) FROM sometable; will be executed by PostgreSQL using a sequential scan of the entire table. The aggregate functions array_agg , string_agg , and xmlagg , as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values.

Comments
  • Put column name in count(col_name)
  • Can you please include the expected result in your question as well?
  • This gives aggregate function calls cannot be nested for MAX(COUNT(*))