CODEWITHSUNDEEP
databasepostgresql
Unknown User
Unknown User

Reputation: 3668

Left Join and Sum between tables giving me a weird result - PostgreSQL

Below are the two tables.

Table1:

Label   value
A   10
A   18
A   15
B   11
B   20
B   10
C   17
C   17
C   18

Table2:

Label   count
A   20
A   17
A   11
B   20
B   17
B   17
C   14
C   20
C   19

I'm running this query.

SELECT
    Table1."label",
    sum("value"),
    sum("count")
FROM Table1
LEFT JOIN Table2 ON
    Table1."label" = Table2."label"
GROUP BY Table1."label"

Result what i need to get is this.

label   value   count
A   43  48
B   41  54
C   52  53

But what i get is.

label   value   count
A   12491   12346
B   213295  1243456
C   1578105 123434

I don't know why I get that result.

Help me out if I'm doing something wrong in the query.

I've just stepped into PostgreSQL and databases.

Upvotes: 0

Views: 612

Answers (1)

digoal.zhou
digoal.zhou

Reputation: 434

Your result has three columns. label, sum(value), sum(count) so 

label   sum
A   12491
B   213295
C   1578105

you see is : 

a 124 91
b 2132 95
c 15781 105

what's the space ? SEE MY EXP : 

digoal=# create table t1(id int, v int);
CREATE TABLE
digoal=# create table t2(id int, c int);
CREATE TABLE
digoal=# insert into t1 values (1,10);
INSERT 0 1
digoal=# insert into t1 values (1,20);
INSERT 0 1
digoal=# insert into t1 values (1,30);
INSERT 0 1
digoal=# insert into t1 values (2,10);
INSERT 0 1
digoal=# insert into t1 values (2,20);
INSERT 0 1
digoal=# insert into t1 values (2,30);
INSERT 0 1
digoal=# insert into t1 values (3,10);
INSERT 0 1
digoal=# insert into t1 values (3,20);
INSERT 0 1
digoal=# insert into t1 values (3,30);
INSERT 0 1
digoal=# insert into t2 select * from t1;
INSERT 0 9
digoal=# select t1.id,sum(v),sum(c) from t1 left join t2 on t1.id=t2.id group by t1.id;
 id | sum | sum 
----+-----+-----
  1 | 180 | 180
  2 | 180 | 180
  3 | 180 | 180
(3 rows)

you can set fieldsep to see.

pg93@db-172-16-3-150-> psql -A
psql (9.3.3)
Type "help" for help.
digoal=# \pset fieldsep ,
Field separator is ",".

digoal=# select t1.id,sum(v),sum(c) from t1 left join t2 on t1.id=t2.id group by t1.id;
id,sum,sum
1,180,180
2,180,180
3,180,180
(3 rows)

Upvotes: 0

Related Questions