Maximum count - PostgreSQL

Question

I'm looking to pull the max(count(*)) of something from a table.

Effectively what i'm trying to do is pull out a customers favourite brand. So they buy 300 bars of soap a year but I'd like to know which their favourite is. So the max(count(brand_id) basically.

I was thinking of doing it like this:

    SELECT
 transaction.customer_id,
 max(occ) 
 FROM
 (  SELECT 
    transaction.customer_id,
    count(transaction.brand_id) as occ,
    FROM
    transaction

    GROUP BY
    transaction.customer_id,

) AS foo
GROUP BY
transaction.customer_id

Thanks in advance

roman · Accepted Answer · 2013-11-08 15:48:55Z

2

you can do it like this:

with cte as (
    select customer_id, brand_id, count(*) as cnt
    from test1
    group by customer_id, brand_id
)
select distinct on (customer_id)
    customer_id, brand_id, cnt
from cte
order by customer_id, cnt desc

Keep in mind, that if there more than one brand with equal count for some customer, you'll end up with one arbitrary record. If you want to get all records, use dense_rank() function:

with cte1 as (
    select customer_id, brand_id, count(*) as cnt
    from test1
    group by customer_id, brand_id
), cte2 as (
    select
        customer_id, brand_id,
        dense_rank() over(partition by customer_id order by cnt desc) as rn
    from cte1
)
select customer_id, brand_id
from cte2
where rn = 1

sql fiddle demo

For PostgreSQL 8.3:

select distinct on (customer_id)
    customer_id, brand_id, cnt
from (
    select customer_id, brand_id, count(*) as cnt
    from test1
    group by customer_id, brand_id  
) as c
order by customer_id, cnt desc;

sql fiddle demo

edited Nov 8, 2013 at 15:48

answered Nov 8, 2013 at 8:46

roman

118k30 gold badges205 silver badges209 bronze badges

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

2 Comments

roman Over a year ago

@QuinnOlive yes, check the sql fiddle

Quinn Olive Over a year ago

My PostgreSQL is 8.3 unfortunately :/

vhadalgi · Accepted Answer · 2013-11-08 08:44:29Z

0

or like this

 with cte as (
    SELECT 
        transaction.customer_id,
        count(transaction.brand_id) as occ,
        FROM
        transaction

        GROUP BY
        transaction.customer_id
    )

    select max(occ) from cte

answered Nov 8, 2013 at 8:44

vhadalgi

7,2376 gold badges44 silver badges67 bronze badges

3 Comments

Quinn Olive Over a year ago

Will this work in PostgreSQL? I keep getting an error on the "AS"

user330315 Over a year ago

@QuinnOlive: yes that should work with any supported version. CTEs where introduced with 8.4.

Quinn Olive Over a year ago

Ahhh. Bugger. My postgreSQL server is 8.3 :/

Collectives™ on Stack Overflow

Maximum count - PostgreSQL

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related