Joining two sql tables on a matching column

Question

I have two sql tables,

table_1 looks like this:

datestamp       items

2020-01-01      1
2020-01-01      33
2020-01-01      245
2020-01-01      55
2020-01-01      534
2020-01-01      35
2020-01-01      35
2020-01-02      10
2020-01-02      100
2020-01-02      50
2020-01-02      10
2020-01-02      1
2020-01-02      166
2020-01-02      76
2020-01-02      67

table_2 looks like this:

datestamp       items_2

2020-01-01      346
2020-01-01      3623
2020-01-02      63
2020-01-02      73

What I am trying to achieve is:

Group items by datestamp for both tables
Join the tables on a datestamp
Create another column which would be items - items_2
Make it all as a view

What I have tried:

select datestamp, SUM(items) from table_1 group by datestamp

select datestamp, SUM(items_2) from table_2 group by datestamp

And it returns what is expected ( numbers are dummy ):

2020-01-01  132
2020-01-02  432
2020-01-03  353


2020-01-01  563
2020-01-02  236
2020-01-03  364

When I try to join the tables:

select table_1.datestamp, table_1.SUM(items), table_2.SUM(items_2)
from table_1
left join table_2
on table_1.datestamp = table_2.datestamp

I get:

Invalid operation: schema "table_1" does not exist

The output I am looking for would look like this ( numbers are dummy ):

datestamp       items_1_sum     items_2_sum     difference

2020-01-01      346             525             items_2_sum-items_1_sum
2020-01-02      3623            352             63  
2020-01-03      63              52              -36
2020-01-04      73              52              -352

Where is my mistake and is there a better way of achieving my desired output?

table_1.SUM(items) is wrong, that would mean a function called sum() in the schema table_1. You want sum(table_1.items) and the same for table_2.SUM(items_2). — sticky bit

nacho nacho · Accepted Answer · 2020-03-30T09:30:34

Your sum functions are wrong applied, and you should use group by:

select table_1.datestamp, sum(table_1.items), sum(table_2.items_2)
from table_1
left join table_2 on table_1.datestamp = table_2.datestamp
group by table_1.datestamp

EDIT I answered too fast, my mistake, it would be better to do

select a.datestamp, sum(a.total), sum(b.total)
from (select datestamp, sum(items) as toatl
      from table_1
      group by datestamp) a
    join (select datestamp, sum(items_2) as total
      from table_2
      group by datestamp) b on a.datestamp = b.datestamp
group by a.datestamp

My first query is wrong because it multiplies the result. When you do the join you get the next result (for the date 2020-01-01):

2020-01-01      1       346
2020-01-01      33      346
2020-01-01      245     346
2020-01-01      55      346
2020-01-01      534     346
2020-01-01      35      346
2020-01-01      35      346
2020-01-01      1       3623
2020-01-01      33      3623
2020-01-01      245     3623
2020-01-01      55      3623
2020-01-01      534     3623
2020-01-01      35      3623
2020-01-01      35      3623

So the values in the first table get multiplied by 2 and the values in the second table get multiplied by 7

Joining two sql tables on a matching column

1 Answers