Finding max count of product

I am trying to find max count of product. The result must only display the brands which have max number of products in it. Can anyone suggest a better way to display result.

Here are the table details:

create table Brand (
    Id integer PRIMARY KEY,
    Name VARCHAR(40) NOT NULL

)


create table Product (

    ID integer PRIMARY KEY,
    Name VARCHAR(40) NOT NULL,
    Price integer NOT NULL,
    BrandId integer NOT NULL,
    CONSTRAINT BrandId FOREIGN KEY (BrandId)
          REFERENCES Brand(Id) MATCH SIMPLE 
          ON UPDATE NO ACTION ON DELETE NO ACTION
)

I have used this SQL query below but I am seeing an error below. The result must display the list of the brands that have max number of products as compared to other brands.

select count(p.id) as count, b.name AS brand_name from product p join brand b on b.Id = p.BrandId
where count(p.id) = (select max(count) from product) 
group by b.name

ERROR: aggregate functions are not allowed in WHERE LINE 2: where count(p.id) = (select max(count) from product) ^ SQL state: 42803 Character: 105

Answer

In Postgres 13+, you can use FETCH WITH TIES:

select count(*) as count, b.name AS brand_name
from product p join
     brand b 
     on b.Id = p.BrandId
group by b.name
order by count(*) desc
fetch first 1 row with ties;

In older versions you can use window functions.

Advertisement

Answer