Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test_table (
 "ID" serial PRIMARY KEY NOT NULL,
 "CID" integer NOT NULL,
 "SEG" integer NOT NULL,
 "DDN" character varying(3) NOT NULL
);

insert into test_table VALUES
 (1 ,1, 1, '711')
,(2, 1, 2, '800')
,(3, 1, 3, '124')
,(4, 2, 1, '711')
,(5, 3, 1, '711')
,(6, 3, 2, '802')
,(7, 4, 1, '799')
,(8, 5, 1, '799')
,(9, 5, 2, '804');

CREATE TABLE



INSERT 0 9


-- new solution
SELECT "DDN"
     , COUNT(*) AS "TOTAL"
     , COUNT(*) FILTER (WHERE seg_count = 1) AS "END"
     , COUNT(*) FILTER (WHERE seg_count > 1) AS "TRA"
FROM  (
   SELECT DISTINCT ON ("CID")
         "DDN"          -- assuming min "SEG" is always 1
       , COUNT(*) OVER (PARTITION BY "CID") AS seg_count
   FROM   test_table
   ORDER  BY "CID", "SEG"
   ) sub
GROUP  BY "DDN";


DDN
TOTAL
END
TRA


799
2
1
1

711
3
1
2



SELECT 2


-- old solution:
WITH x AS (
    SELECT
        (SELECT t1."DDN" FROM test_table AS t1
         WHERE t1."CID"=t."CID" AND t1."SEG"=1) AS ddn,
        COUNT("CID") AS seg_count       
    FROM test_table AS t
    GROUP BY "CID"
)

SELECT ddn, COUNT(seg_count) AS "TOTAL",    
    SUM(CASE WHEN x.seg_count=1 THEN 1 ELSE 0 END) as "END",
    SUM(CASE WHEN x.seg_count>1 THEN 1 ELSE 0 END) as "TRA"
FROM x
GROUP BY ddn;


ddn
TOTAL
END
TRA


799
2
1
1

711
3
1
2



SELECT 2