I have the following data:
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
Then i do the following query:
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;
The result is correct:
objectid guid_count cuid_count umdid_count 9345783451 4 3 10
Then i add two column, name and value:
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;
That return:
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
What i expect is:
objectid guid_count cuid_count umdid_count acn 9345783451 4 3 10 12433792
If i add group by on guid_count, cuid_count, and umdid_count:
[42803] ERROR: aggregate functions are not allowed in GROUP BY Position: 26
How to overcome this problem? Could you help point me where my mistake is?
Thank you.
Advertisement
Answer
You should use an aggregate for the added column, e.g.:
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,
max(case
when identifier.name = 'acn' then identifier.value::text
else '' end) as acn
from identifier
where objectid = '9345783451'
group by objectid;