CODEWITHSUNDEEP
sqlpostgresql-9.4cartesian-product
sh4rkyy
sh4rkyy

Reputation: 402

Avoid cartesian product using sum

I want to sum up the stake from tickets table, grouping it by customer_id and date_trunc('day') from bonus table.

The problem is that rows are being multiplied and I don't know how to solve it.

https://www.db-fiddle.com/f/yWCvFamMAY9uGtoZupiAQ/4 

CREATE TABLE tickets (
    ticket_id integer,
    customer_id integer,
  stake integer,
  reg_date date
);

CREATE TABLE bonus (
bonus_id integer,
customer_id integer,
reg_date date
);

insert into tickets 
values
(1,100, 12,'2019-01-10 11:00'),
(2,100, 10,'2019-01-10 12:00'),
(3,100, 30,'2019-01-10 13:00'),
(4,100, 10,'2019-01-11 14:00'),
(5,100, 15,'2019-01-11 15:00'),

(6,102, 25,'2019-01-10 10:00'),
(7,102, 25,'2019-01-10 11:10'),
(8,102, 13,'2019-01-11 12:40'),
(9,102, 9,'2019-01-12 15:00'),
(10,102, 7,'2019-01-13 18:00'),


(13,103, 15,'2019-01-12 19:00'),
(14,103, 11,'2019-01-12 22:00'),
(15,103, 11,'2019-01-14 02:00'),
(16,103, 11,'2019-01-14 10:00')
;

insert into bonus
values
(200,100,'2019-01-10 05:00'),
(201,100,'2019-01-10 06:00'),
(202,100,'2019-01-10 15:00'),
(203,100,'2019-01-10 15:50'),
(204,100,'2019-01-10 16:10'),
(205,100,'2019-01-10 16:15'),
(206,100,'2019-01-10 16:22'),
(207,100,'2019-01-11 10:10'),
(208,100,'2019-01-11 16:10'),

(209,102,'2019-01-10 10:00'),
(210,102,'2019-01-10 11:00'),
(211,102,'2019-01-10 12:00'),
(212,102,'2019-01-10 13:00'),

(213,103,'2019-01-11 11:00'),
(214,103,'2019-01-11 18:00'),
(215,103,'2019-01-12 15:00'),
(216,103,'2019-01-12 16:00'),
(217,103,'2019-01-14 02:00')




select 
customer_id, 
date_trunc('day', b.reg_date), 
sum(t.stake)

from tickets t
join bonus b using (customer_id)
where date_trunc('day', b.reg_date) = date_trunc('day', t.reg_date)
group by 1,2
order by 1

Output for customer 102 should be:

102,2019-01-10, 50

Upvotes: 2

Views: 500

Answers (1)

Shawn.X
Shawn.X

Reputation: 1363

OK, I think you want to get the summary data of column stake in tickets table and the records's customer_id, reg_date pairs have appeared in the second table bonus, and all business has nothing to do with the bonus_id, am I right? The customer_id, reg_date pairs in bonus is duplicated, so you need a distinct on it, and then join the sum data from tickets.The complete SQL and result as below:

with stake_sum as (
select
    customer_id,
    reg_date,
    sum(stake)
from
    tickets
group by
    customer_id,
    reg_date
)
,bonus_date_distinct as (
select
    distinct customer_id,
    reg_date
from
    bonus
)
select
    a.*
from
    stake_sum a
join
    bonus_date_distinct b on a.customer_id = b.customer_id and a.reg_date = b.reg_date order by customer_id, reg_date;
 customer_id |  reg_date  | sum 
-------------+------------+-----
         100 | 2019-01-10 |  52
         100 | 2019-01-11 |  25
         102 | 2019-01-10 |  50
         103 | 2019-01-12 |  26
         103 | 2019-01-14 |  22
(5 rows)

Upvotes: 1

Related Questions