This is the next version of Sum on distinct and group by

But It goes deeper

I have this :

CREATE TABLE active_connections(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL); INSERT INTO active_connections VALUES (0, 'web-mobile', 1); INSERT INTO active_connections VALUES (1, 'web-desktop', 1); INSERT INTO active_connections VALUES (2, 'web-desktop', 2); INSERT INTO active_connections VALUES (3, 'web-desktop', 3); INSERT INTO active_connections VALUES (4, 'web-desktop', 3); INSERT INTO active_connections VALUES (5, 'native', 3); INSERT INTO active_connections VALUES (6, 'native', 4); 

What I’m trying to do is to group distinct user per native vs 'web-desktop' and 'web-mobile'.

I would like to get

source  user_count native  2 web     3 

I’m able to do it only if I alias the source column to something different. I don’t understand why

SELECT  CASE   WHEN source IN ('web-mobile', 'web-desktop') THEN 'web'   ELSE source   END AS source_2, COUNT(DISTINCT user_id) AS user_count FROM active_connections GROUP BY source_2; 

First why I can say END AS source? Second can I do it without using a subquery like below to be able to have source column?

WITH mapped_source AS ( SELECT  CASE   WHEN source IN ('web-mobile', 'web-desktop') THEN 'web'   ELSE source   END AS web_or_native, COUNT(DISTINCT(user_id)) AS user_count FROM active_connections GROUP BY web_or_native )  SELECT web_or_native AS source, user_count FROM mapped_source; 

Rextester : http://rextester.com/FRB66163