I have a table:

 id |  name  | type
  1 | Ali    |    a
  1 | Sami   |    a
  3 | Kamil  |    c
  3 | Imran  |    c
  2 | Wali   |    b
  4 | Yousuf |    d
  5 | Kamran |    e
  6 | Asad   |    f
  6 | Dawood |    f
  7 | Asid   |    g
  8 | Sadaf  |    g

I want to sum the values of column 'type', but the value of column 'id' should be distinct.

For example: I want to sum the values of type(a,b,d) into one column me1 and values of type(c,e,f,g) to another column me2, but those values should be summed which have distinct ids.

My desired result:

 me1 | me2
  3  | 5

me1 becomes 3 because it sums type(a,b,d). type a occurs twice, but we sum it only once because we sum the type which has distinct id. type b occurs once and type d occurs once so the total is 3. same is the case with me2 where we sum 1+1+1+2 for each type where the id is distinct.


As you describe, first do the distinct in a common-table-expression. Then query that for the sums:

create table me(id int, name text, type char(1));

insert into me values
(1, 'Ali', 'a'),
(1, 'Sami', 'a'),
(3, 'Kamil', 'c'),
(3, 'Imran', 'c'),
(2, 'Wali', 'b'),
(4, 'Yousuf', 'd'),
(5, 'Kamran', 'e'),
(6, 'Asad', 'f'),
(6, 'Dawood', 'f'),
(7, 'Asid', 'g'),
(8, 'Sadaf', 'g');

with d as (
  select distinct id, type
  from me
  (select count(*) as me1 from d where type in ('a','b','d')),
  (select count(*) as me2 from d where type in ('c','e','f','g'));

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.