SQL Query Summing In Join

Question

I have two tables

Table_1:

╔══════╦══════════╦═════════╗
║ Name ║   Date   ║ Revenue ║
╠══════╬══════════╬═════════╣
║  A   ║ 1/1/2001 ║   20    ║
║  A   ║ 1/2/2001 ║   20    ║
║  B   ║ 1/1/2001 ║   40    ║
╚══════╩══════════╩═════════╝

Table_2:

╔══════╦══════╗
║ Name ║ Task ║
╠══════╬══════╣
║  A   ║ Call ║
║  A   ║ Foo  ║
║  B   ║ Bar  ║
╚══════╩══════╝

So I do a join

SELECT sum(Revenue), t2.Name, T2.Task
FROM Table_1 as t1 JOIN Table_2 as t2 ON t1.Name = t2.Name
GROUP BY t2.Name

The result table of the join looks like this:

Result
Name    Sum
A       80
B       40

The problem is that I want the sum result of A to be 40. How should I modify my query?

Tim Biegeleisen · Accepted Answer · 2015-11-11 04:46:36Z

2

Use this query:

SELECT Name, SUM(Revenue)
FROM Table_1
GROUP BY Name

I don't see any point in joining Table_1 to Table_2 since you are not making use of the Task column.

answered Nov 11, 2015 at 4:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kyle Hale Over a year ago

Perhaps he's using it as a filter i.e. only show rows which also exist in Table_2.

Kyle Hale Over a year ago

Also you should explain why he got 80 in his query, it's a good teaching opportunity.

Kyle Hale Over a year ago

He gets 80 because when he joins on name, it returns 4 rows where Name = A (2 rows from table_1 times 2 rows from table_2). 4 * 20 = 80

Tim Biegeleisen Over a year ago

@Kyle Of course you are right. Thanks for pointing this out.

Rhs Over a year ago

Forgot to add Task to the select clause.

|

user5151179 · Accepted Answer · 2015-11-11 05:04:07Z

2

The root of your problem is that the join is not doing what you expect it to do. By doing the join between the two tables on the 'name', you are creating duplicates. Remove the group by clause in your query and you will see exactly what I mean.

As mentioned in a previous answer, the join (in this case) is superfluous. I would advise looking at things closer than that. How could the data be structured such that this duplication of data doesn't occur?

Without more data I can't provide you any more direction, but hopefully my comments will point you in the right direction and you'll learn a valuable lesson on using discrete key values.

answered Nov 11, 2015 at 5:04

user5151179

5852 silver badges10 bronze badges

1 Comment

Rhs Over a year ago

Forgot to add Task to the select clause

Mauri · Accepted Answer · 2015-11-16 15:46:38Z

0

you have to calculate the sum before joining the tables like:

    select t1.Name, t1.sum, t2.task from
    (
      SELECT Name, sum(Revenue) as sum FROM Table_1 group by Name
    ) as t1 
   JOIN 
   Table_2 as t2 ON t1.Name = t2.Name

edited Nov 16, 2015 at 15:46

answered Nov 15, 2015 at 20:34

Mauri

1,2851 gold badge14 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL Query Summing In Join

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related