return specific value from id column to create new column-CodePudding

I have this example data to use it to make it easier

CREATE TABLE example (
id_ TEXT ,
product TEXT ,
price numeric (10));

INSERT INTO example (id_, product, price)
values
('1a','Soap', 200),
('1a','Conditioner', 300),
('1a','Shampoo', 250),
('1a','Soap', 100),
('2a','Toothbrush', 150),
('2a','Soap', 200),
('2a','Mouthwash',350),
('3a','Shampoo',250),
('3a','Perfume',400);

Normally, most of all will use this code

SELECT id_, product, sum(price)total  
FROM example     
group by id_, product  
order by id_, product;

so it will get this kind of output

id_     product     total
1a  Conditioner     300
1a  Shampoo         250
1a  Soap            300
2a  Mouthwash       350
2a  Soap            200
2a  Toothbrush      150
3a  Perfume         400
3a  Shampoo         250

is there any way to make it like this, maybe use case when to make it simple ?
will give 1 if different id_, and give 0 if same id_,

id_     product     total   tot_cust
1a  Conditioner     300     1
1a  Shampoo         250     0
1a  Soap            300     0
2a  Mouthwash       350     1
2a  Soap            200     0
2a  Toothbrush      150     0
3a  Perfume         400     1
3a  Shampoo         250     0

CodePudding user response：

What you want is a window function:

SELECT
  id_,
  product,
  sum(price) total,
  (row_number() OVER (PARTITION BY id_ ORDER BY product) = 1)::int AS tot_cust
FROM example     
GROUP BY id_, product  
ORDER BY id_, product