CODEWITHSUNDEEP
sqljoin
Avihai Marchiano
Avihai Marchiano

Reputation: 3927

How to generate sum report from joining of 2 tables where the join key is not unique

I have 2 tables that collect records of event on points.

CREATE TABLE report_one
(
  date timestamp,
  point_id bigint,
  income int
 )
CREATE TABLE report_two
(
  date timestamp,
  point_id bigint,
  spent int
 )

I want to generate a sum report (and addiitonal reports). i want to use join because i need to support pagination, ordering ...

The problem is that the join key (point id for the report) is not 1:1 , so i got the same row more than one.

insert into report_one values('2013-1-1',1,1)
insert into report_two values('2013-1-1',1,1)
insert into report_two values('2013-1-2',1,1)

select * from report_one r1 left join report_two r2 on r1.point_id  = r2.point_id

will have 2 rows of table report_one ,but for total i need only one. I want to be able to create a view of some kind of join between the tables, where each row will be only once.

**I want output like this:

1 (pid) , 1,1,0,0 - this from report_one

1 (pid) ,0,0,1,1 -- this from report_two

1 (pid) ,0,0,1,1 -- this from report_two **

Union all can be great , but i dont have the same columns types in the two tables.

Ps . The real table has lots of column and pk is more than one column , i just make is simple for the question

Upvotes: 0

Views: 176

Answers (3)

Pavel Malinnikov
Pavel Malinnikov

Reputation: 359

You could group tables by point_id first, choosing more appropriate aggregate functions for the fields needed and then join to each other:

select r1.point_id, r1.date, r1.income, r2.spent
from
(
   select point_id, max(date) date, sum(income) income
   from report_one
   group by point_id
) r1 
    inner join
    (
       select point_id, max(date) date, sum(spent) spent
       from report_two
       group by point_id
    ) r2 on r1.point_id = r2.point_id

Also, UNION way:

select point_id, date, income sum, 1 is_income
   from report_one
union all
select point_id, date, spent sum, 0 is_income
       from report_two

Upvotes: 0

Avihai Marchiano
Avihai Marchiano

Reputation: 3927

I think that this can work for me:

select date d1,point_id p1,0 income ,spent spent from report_one
union ALL
select date d2,point_id p2,income,0 spent from report_two

I dont have to have the zero . I added them for demo a case that they the columns are not from the same type

Upvotes: 0

Conrad Lotz
Conrad Lotz

Reputation: 8828

Why not try the following.

CREATE TABLE report
(
  report_id bigint,
  date varchar(20),
  point_id bigint,
  amount int,
  amount_type varchar(20)
 );

THEN

   insert into report values (1,'2013-01-01',1,1,'income');
   insert into report values (2,'2013-01-01',1,1,'expense');
   insert into report values (2,'2013-01-02',1,1,'expense');

Finally

SELECT report_id,amount_type,SUM(point_id) FROM report GROUP BY report_id,amount_type

The output will sum point_id per report/amount_type then it will be easier to draw stats per date range, etc and overhead by create table and joins will also be minimized.

The output: SQL Fiddle Demo

Upvotes: 1

Related Questions