title: How to fix aggregate functions are not allowed in GROUP BY?
body: <p>I have the following data:</p>
  
  <pre><code>table: identifier
  
  objectid    type    name            value
  9345783451  GUID    msisdn          bc3b2286379da25fd6ef3bac120827589c783
  9345783451  UMID    umid            f701a99c-96c9-4d4a-8508-932eeeaca77c
  9345783451  UMDID   umdid           2840f267-4bdd-4af4-8b36-72badbce1e11
  9345783451  UMDID   umdid           bbe467f5-0fc4-4624-8b01-dd656767d3b2
  9345783451  GUID    encrypted       ZPmBF2Spq8DBX0wl
  9345783451  UMDID   umdid           a4c6b3cb-938f-4ae5-8246-f2612ffd946b
  9345783451  UMDID   umdid           2a8e5f98-5a74-431e-bbaf-7c75fdeb991a
  9345783451  UMDID   umdid           269a39b9-1122-4d08-9ca4-36f6c8e00e8e
  9345783451  COOKIE  amcv            39852788960115122553605179944081330813
  9345783451  UMDID   umdid           7715969f-63ab-4540-952c-73beafb46024
  9345783451  GROUPID ban-sha2        1d98f855e9a4fbeba1937f774b6dbab2ca194b5
  9345783451  COOKIE  token           21agqB6x_H8.1575825731298.aXHr4GoVupopE
  9345783451  GUID    acn             12433792
  9345783451  UMDID   umdid           f2cf7402-21d2-44ea-b432-66e997cfebbf
  9345783451  GUID    targetId        1255625699
  9345783451  UMDID   umdid           b8d55cdd-4a95-4e07-8eeb-f281a0961a6a
  9345783451  UMDID   umdid           af890608-b512-4a96-9274-f39f388ff442
  9345783451  UMDID   umdid           0c55ecb1-e24e-419d-97a1-48f6eba45fe0
  </code></pre>
  
  <p>Then i do the following query:</p>
  
  <pre><code>select objectid,
             count(objectid) filter ( where type = 'GUID' )                                                                     as guid_count,
             count(objectid) filter ( where type = 'GUID' and (name in
                                                               ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) ) as cuid_count,
             count(objectid) filter ( where name = 'umdid' )                                                                    as umdid_count
      from identifier
      where objectid = '9345783451'
      group by objectid;
  </code></pre>
  
  <p>The result is correct:</p>
  
  <pre><code>objectid    guid_count cuid_count umdid_count
  9345783451  4          3          10
  </code></pre>
  
  <p>Then i add two column, name and value:</p>
  
  <pre><code>select objectid,
                 count(objectid) filter ( where type = 'GUID' )                                                                     as guid_count,
                 count(objectid) filter ( where type = 'GUID' and (name in
                                                                   ('acn', 'encrypted', 'kdid', 'msisdn', 'targetId')) ) as cuid_count,
                 count(objectid) filter ( where name = 'umdid' )                                                                    as umdid_count,
                 case
                     when identifier.name = 'acn' then identifier.value::text
                     else '' end                                                                                                                          as acn
          from identifier
          where objectid = '9345783451'
          group by objectid, name, value;
  </code></pre>
  
  <p>That return:</p>
  
  <pre><code>objectid   guid_count cuid_count umdid_count acn
  9345783451  0          0         1           "" 
  9345783451  1          0         0           "" 
  9345783451  0          0         1           "" 
  9345783451  0          0         0           "" 
  9345783451  0          0         1           "" 
  9345783451  1          1         0           12433792 
  9345783451  0          0         1           "" 
  9345783451  0          0         1           "" 
  9345783451  1          1         0           "" 
  9345783451  0          0         0           "" 
  9345783451  0          0         1           "" 
  9345783451  0          0         1           "" 
  9345783451  0          0         0           "" 
  9345783451  0          0         1           "" 
  9345783451  0          0         1           "" 
  9345783451  0          0         0           "" 
  9345783451  1          1         0           "" 
  9345783451  0          0         1           ""
  
  "" empty string
  </code></pre>
  
  <p>What i expect is:</p>
  
  <pre><code>objectid    guid_count cuid_count umdid_count acn
  9345783451  4          3          10          12433792
  </code></pre>
  
  <p>If i add group by on guid_count, cuid_count, and umdid_count:</p>
  
  <pre><code>[42803] ERROR: aggregate functions are not allowed in GROUP BY Position: 26
  </code></pre>
  
  <p>How to overcome this problem? Could you help point me where my mistake is?</p>
  
  <p>Thank you.</p>