Sum on distinct and group by

All we need is an easy explanation of the problem, so here it is.

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
``````

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You need to group by a substring of source:

``````SELECT shop_id,
substring(source,1,3),
COUNT(DISTINCT user_id) AS user_count
FROM tickets
WHERE shop_id = 2
GROUP BY shop_id, substring(source,1,3);
``````

http://rextester.com/EFZZSG27212

Method 2

What you want is something like:

``````SELECT
shop_id,
user_id,
COUNT(user_id)  AS user_count,
LEFT(source, 4) AS new_source
FROM tickets
GROUP BY (user_id, shop_id, new_source)
ORDER BY user_id;
``````

Result: (check the db-fiddle here)

``````shop_id     user_id     user_count  new_source
2           1              2        web-
2           2              1        web-
``````

Of course you can use `SUBSTRING` instead of `LEFT` as suggested.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂