title: Group and count by another columns value
body: <p>I have a table like below:</p>
  
  <pre><code>CREATE TABLE public.test_table
  (
      "ID" serial PRIMARY KEY NOT NULL,
      "CID" integer NOT NULL,
      "SEG" integer NOT NULL,
      "DDN" character varying(3) NOT NULL
  )
  </code></pre>
  
  <p>and data looks like this:</p>
  
  <pre><code>ID  CID SEG DDN
  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"
  10  6   1   "799"
  </code></pre>
  
  <p>I need to group these data by CID column and get column counts depends on DDN columns first values but counts must give me two different information, if it's more than 1 or not. </p>
  
  <p>I'm really sorry if couldn't explains clearly. Let me show you what I need..</p>
  
  <pre><code>DDN END TRA
  711 1   2
  799 2   1
  </code></pre>
  
  <p>As you can see, DDN:711 has 1 record of single count (ID:4). This is END column.
  But 2 times has multiple SEG count (ID:1to3 and ID:5to6). This is TRA column.</p>
  
  <p>I can not be sure what column should be in group clause! </p>
  
  <h3>My solution:</h3>
  
  <p>Just found a solution like below</p>
  
  <pre><code>WITH x AS (
      SELECT
          (SELECT t1."DDN" FROM public.test_table AS t1
           WHERE t1."CID"=t."CID" AND t1."SEG"=1) AS ddn,
          COUNT("CID") AS seg_count       
      FROM public.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&gt;1 THEN 1 ELSE 0 END) as "TRA"
  FROM x
  GROUP BY ddn;
  </code></pre>