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
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.
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);
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 🙂