-1

I have table

id    name    brand_id
1    name1    1
2    name2    2
3    name3    3
4    name3    3
5    name4    1

id 3 and id 4 are duplicates with same name and brand_id. I want to count DISNITC without duplicates - I use query

SELECT DISTINCT brand_id, COUNT(brand_id)
FROM z.test
GROUP BY brand_id

I get result:

brand_id  COUNT(brand_id)
1          2
2          1
3          2 (but I need to get 1 here) 

Please help solve problem. Thanks.

2
create table tbl(id int, name varchar(10), brand_id int);
insert into tbl values
(1, 'name1', 1),
(2, 'name2', 2),
(3, 'name3', 3),
(4, 'name3', 3),
(5, 'name4', 1);
select brand_id, count(distinct name)
from   tbl
group by brand_id;
brand_id | count(distinct name)
-------: | -------------------:
       1 |                    2
       2 |                    1
       3 |                    1

db<>fiddle here

1

You should not have allowed duplicate rows in the first place. UNIQUE(name, brand) would have prevented it.

It sounds like there are 2 steps.

  1. Get rid of dups: SELECT DISTINCT name, brand_id FROM tbl

  2. Count the frequency of each brand:

    SELECT  brand,
            COUNT(*)
    FROM ( SELECT DISTINCT name, brand_id FROM tbl ) AS x
    GROUP BY brand_id
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.