sql - 计算 CASE WHEN 的输出

Question

我需要对 psql 实例的查询输出进行计数。

我已经尝试将它变成许多不同配置的子查询。

我想获取此查询的输出并对新创建的测试组列进行计数。

SELECT userid,
   CASE WHEN COUNT(type) filter (WHERE type = 'web') <= COUNT(type) filter (WHERE type = 'mobile') THEN 'GROUPA'
   ELSE 'GROUPB'
   END AS testgroup  FROM logins
   GROUP BY userid
   ORDER BY userid;

输出

期望的输出

Answer 1

一种方法是子查询:

SELECT testgroup, COUNT(*)
FROM (SELECT userid,
             (CASE WHEN COUNT(type) filter (WHERE type = 'web') <= COUNT(type) filter (WHERE type = 'mobile')
                   THEN 'GROUPA'
                   ELSE 'GROUPB'
              END) AS testgroup
     FROM logins
     GROUP BY userid
    ) u
GROUP BY testgroup