I have this :
CREATE TABLE tickets(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL, shop_id int NOT NULL, created_at time DEFAULT current_time);
INSERT INTO tickets VALUES (0, 'web-mobile', 1, 2);
INSERT INTO tickets VALUES (1, 'web-destkop', 1, 2);
INSERT INTO tickets VALUES (2, 'web-destkop', 2, 2);
SELECT shop_id, source, COUNT(DISTINCT(user_id)) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, source;
Rexster : http://rextester.com/TDS9913
But what I would like is to get the sum of distinct user_id
on web-*
.
shop_id source user_count
2 web 2
Best Answer
You need to group by a substring of source:
http://rextester.com/EFZZSG27212