Sum columns from two tables in sql

Question

I have two tables, one is the cost table and the other is the payment table, the cost table contains the cost of product with the product name.

 Cost Table
  id    |   cost   |  name
   1    |   100    |   A
   2    |   200    |   B
   3    |   200    |   A


  Payment Table
  pid  | amount    | costID
  1    |   10      |   1
  2    |   20      |   1
  3    |   30      |   2
  4    |   50      |   1

Now I have to sum the total of cost by the same name values, and as well sum the total amount of payments by the costID, like the query below

 totalTable

name | sum(cost)  |  sum(amount) |
  A  |  300       |     80       |
  B  |  200       |     30       |

However I have been working my way around this using the query below but I think I am doing it very wrong.

                SELECT 
                    b.name,
                    b.sum(cost),
                    a.sum(amount)

                FROM 
                      `Payment Table` a

                 LEFT JOIN
                      `Cost Table` b 
                ON   
                      b.id=a.costID


                      GROUP by b.name,a.costID

I would be grateful if somebody would help me with my queries or better still an idea as to how to go about it. Thank you

I don't think you need to group on a.costID - if you remove that, does it work? — Tyler Roper
– Tyler Roper, Commented Oct 5, 2016 at 15:41
You need to "Sum" before you join. as the one to many join between cost and payments ic causing an inflated sums. — xQbert
– xQbert, Commented Oct 5, 2016 at 15:42

souslov · Accepted Answer · 2016-10-05 16:06:03Z

1

This should work:

select t2.name, sum(t2.cost), coalesce(sum(t1.amount), 0) as amount
from (
   select id, name, sum(cost) as cost
   from `Cost`
   group by id, name
) t2
left join (
   select costID, sum(amount) as amount
   from `Payment`
   group by CostID
) t1 on t2.id = t1.costID
group by t2.name

SQLFiddle

edited Oct 5, 2016 at 16:06

answered Oct 5, 2016 at 15:53

souslov

44.7k11 gold badges92 silver badges113 bronze badges

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

Comments

Juan Carlos Oropeza · Accepted Answer · 2016-10-05 16:01:41Z

1

You need do the calculation in separated query and then join them together.

First one is straight forward.
Second one you need to get the name asociated to that payment based in the cost_id

SQL Fiddle Demo

SELECT C.`name`, C.`sum_cost`, COALESCE(P.`sum_amount`,0 ) as `sum_amount`
FROM (
    SELECT `name`, SUM(`cost`) as `sum_cost`
    FROM `Cost`
    GROUP BY `name`
    ) C
LEFT JOIN (    
    SELECT `Cost`.`name`, SUM(`Payment`.`amount`) as `sum_amount`
    FROM `Payment`
    JOIN `Cost` 
       ON `Payment`.`costID` = `Cost`.`id`
    GROUP BY `Cost`.`name`
  ) P
  ON C.`name` =  P.`name`

OUTPUT

| name | sum_cost | sum_amount |
|------|----------|------------|
|    A |      300 |         80 |
|    B |      200 |         30 |

answered Oct 5, 2016 at 16:01

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

3 Comments

MessiahCoder Over a year ago

Thanks this also works well, thanks for your effort +1

MessiahCoder Over a year ago

I wish i could accept both answers, thanks once again

Juan Carlos Oropeza Over a year ago

Well is up to you to choose which one accept based on what you believe solve better your question. I can argue my answer is better because at least try to explain what is happening instead just the code. Also even when potashin say submited first, he deleted his first answer because was wrong. And is until the final edit 4 min after mine his answer is correct. Even he use my sqlFiddle to test the solution.

spencer7593 · Accepted Answer · 2016-10-05 16:30:59Z

0

A couple of issues. For one thing, the column references should be qualified, not the aggregate functions.

This is invalid:

 table_alias.SUM(column_name)

Should be:

 SUM(table_alias.column_name)

This query should return the first two columns you are looking for:

SELECT c.name         AS `name`
     , SUM(c.cost)    AS `sum(cost)`
  FROM `Cost Table` c
 GROUP BY c.name
 ORDER BY c.name

When you introduce a join to another table, like Product Table, where costid is not UNIQUE, you have the potential to produce a (partial) Cartesian product.

To see what that looks like, to see what's happening, remove the GROUP BY and the aggregate SUM() functions, and take a look at the detail rows returned by a query with the join operation.

  SELECT c.id               AS `c.id`
       , c.cost             AS `c.cost`
       , c.name             AS `c.name`
       , p.pid              AS `p.pid`
       , p.amount           AS `p.amount`
       , p.costid           AS `p.costid`
    FROM `Cost Table` c
    LEFT
    JOIN `Payment Table` p
      ON p.costid = c.id
   ORDER BY c.id, p.pid

That's going to return:

 c.id | c.cost |  c.name | p.pid | p.amount | p.costid
 1    |    100 |  A      | 1     |       10 | 1       
 1    |    100 |  A      | 2     |       20 | 1       
 1    |    100 |  A      | 4     |       50 | 1       
 2    |    200 |  B      | 3     |       30 | 2
 3    |    200 |  A      | NULL  |     NULL | NULL

Notice that we are getting three copies of the id=1 row from Cost Table.

So, if we modified that query, adding a GROUP BY c.name, and wrapping c.cost in a SUM() aggregate, we're going to get an inflated value for total cost.

To avoid that, we can aggregate the amount from the Payment Table, so we get only one row for each costid. Then when we do the join operation, we won't be producing duplicate copies of rows from Cost.

Here's a query to aggregate the total amount from the Payment Table, so we get a single row for each costid.

  SELECT p.costid
       , SUM(p.amount) AS tot_amount
    FROM `Payment Table` p 
   GROUP BY p.costid
   ORDER BY p.costid

That would return:

  costid | tot_amount
  1      | 80
  2      | 30

We can use the results from that query as if it were a table, by making that query an "inline view". In this example, we assign an alias of v to the query results. (In the MySQL venacular, an "inline view" is called a "derived table".)

SELECT c.name                      AS `name`
     , SUM(c.cost)                 AS `sum_cost`
     , IFNULL(SUM(v.tot_amount),0) AS `sum_amount`
  FROM `Cost Table` c
  LEFT
  JOIN ( -- inline view to return total amount by costid 
         SELECT p.costid
              , SUM(p.amount) AS tot_amount
           FROM `Payment Table` p
          GROUP BY p.costid
          ORDER BY p.costid
       ) v
    ON v.costid = c.id
 GROUP BY c.name
 ORDER BY c.name

edited Oct 5, 2016 at 16:30

answered Oct 5, 2016 at 16:23

spencer7593

109k15 gold badges122 silver badges148 bronze badges

7 Comments

Juan Carlos Oropeza Over a year ago

Great explain. But two things a) You need use LEFT JOIN you can have Cost without Payments. b) name can have different id, but not sure if that is a typo error.

spencer7593 Over a year ago

@JuanCarlosOropeza: good catch, i missed the outer join on that last query (fixed). Also added IFNULL around the SUM(), to get zero returned in place of NULL when there are no matching rows in payments. I'm not seeing any issue issue with name vs id (could be something I missed. Query designed around OP specification, appeared to be GROUP BY name, even though we are joining on c.id=p.costid.

Juan Carlos Oropeza Over a year ago

Im not sure, so is probably better test it on the Fiddle. But because you are joining for CostID this is the case Im worried if [{name: A, Cost:1, Sum: SomeNumber}, {name: A, Cost:3, Sum: Null}, the the group by name will be null.

spencer7593 Over a year ago

GROUP BY c.name will work fine. The SUM(v.tot_amount) will be fine. That will include rows with cost rows id in (1,3), you're right that tot_amount for id=3 will be NULL, but the SUM() aggregate will handle the NULL value. You are right to point out that if we were to use addition operation with a NULL, e.g. 800 + NULL, the result would be NULL. But the SUM(v.tot_amount) will be 80. (If we used COUNT(v.tot_amount), that would return 1. If we used AVG(v.tot_amount), that would return as 80. The expression AVG(IFNULL(v.tot_amount,0)) would return 40.

spencer7593 Over a year ago

What the query would omit would be amount from rows in Payment that are not associated with Cost. e.g. a row in Payment like this (pid=5,amount=420,costid=7) will not match a row in Cost, if there is no row in Cost with id=7. With the outer join, it depends which table we want to be the driver. The queries above return all rows that are in Cost, along with matching rows from Payment. Rows in Payment that don't match a row in Cost will be excluded.

|

Collectives™ on Stack Overflow

Sum columns from two tables in sql

3 Answers 3

Comments

3 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related