count real total customer by product groupby

Question

first of all, i'm not really sure if this possible or not.
let say I have this dataset example

CREATE TABLE TRANSACTION(
user_id numeric,
account_id varchar,
product varchar,
colour varchar,
price numeric);
insert into transaction (user_id, account_id, product, colour, price)
values
(1, 'a1', 'biycle', 'black', 500),
(1, 'a2', 'motorbike', 'red', 1000),
(1, 'a2', 'motorbike', 'blue', 1200),
(2, 'b3', 'car', 'grey', 10000),
(2, 'b2', 'motorbike', 'black', 1250),
(3, 'c1', 'biycle', 'black', 500),
(3, 'c2', 'biycle', 'black', 525),
(3, 'c4', 'skateboard', 'white', 250),
(3, 'c5', 'scooter', 'blue', 260)

from that table we know that
the total real customer is 3 (1,2,3) and
the total real account is 8 (a1, a2, b3, b2, c1, c2, c4, c5)
and then with this code

SELECT
    product,
    colour,
    sum(price)total_price,
    count(DISTINCT user_id)customer_total,
    count(DISTINCT account_id)account_total
 from transaction
 group by 
    product, colour

and the return is like this

product	colour	total_price	customer_total	account_total
biycle	black	1525	2	3
car	grey	10000	1	1
motorbike	black	1250	1	1
motorbike	blue	1200	1	1
motorbike	red	1000	1	1
scooter	blue	260	1	1
skateboard	white	250	1	1

from the output above,
if we total the customer_total, it will be 8 and
if we total the account_total, it will be 9

is there any alternative way so that the customer_total will be 3 and account_total will be 8

Answer 1

You can calculate the accounts and customer total using an inline query that computes the customer and account totals within the same query.

SELECT
    product,
    colour,
    sum(price)total_price,
    (select count(DISTINCT  user_id) from transaction) as customer_total,
    (select count(DISTINCT account_id) from transaction) as account_total
 from transaction
 group by 
    product, colour

Result:

product	colour	total_price	customer_total	account_total
biycle	black	1525	3	8
car	grey	10000	3	8
motorbike	black	1250	3	8
motorbike	blue	1200	3	8
motorbike	red	1000	3	8
scooter	blue	260	3	8
skateboard	white	250	3	8