In an application where a user can have copies of a mobile application installed on multiple devices I'd like to know how many iphone and how many android apps each user has.
I tried a query such as
gv2=> SELECT userid,
COUNT(ALL version_identifier LIKE '%ios%'),
COUNT(ALL version_identifier LIKE '%android%')
FROM gl.user_device
GROUP BY userid;
userid | count | count
--------------------------------------+-------+-------
46d0f5b7-42b0-4aad-9162-1390c32cb06e | 7 | 7
5d519794-abfe-4863-82d4-6da33db7637b | 7 | 7
a81cff6b-30f2-4b6e-a5bf-b1a933904473 | 1 | 1
b65f0708-0cd1-11e7-878b-06fa189da46b | 4 | 4
94b91b02-ff43-4a9a-b317-037fa2a347d3 | 1 | 1
a4cacd98-1216-4801-b058-b28b8fa632a9 | 8 | 8
(6 rows)
Which is clearly wrong.
For example
gv2=> SELECT userid FROM gl.user_device WHERE version_identifier LIKE '%ios%';
userid
--------------------------------------
5d519794-abfe-4863-82d4-6da33db7637b
(1 row)
In case it matters I need to support Postgres 10.1 but preferably also 9.5.10 as I have one old Production DB still on that version.
FILTER
clause here: postgresql.org/docs/10/sql-expressions.html#SYNTAX-AGGREGATES – Abelisto Jan 20 '19 at 18:05