Sum query with reference to different coloumn for each row?

Question

Is it possible to carry out a sum query where the row for each part of the sum is determine from a join?

For example if I have tables

table A
id | value
1  |  10
2  |  15
3  |  10

And

table b
id  | b  | c
1  | 2  | 3
2  | 1  | 2

Is it possible to do a SUM(tableA.value * tableB.<specific_column>) Where either the SUM is carried out directly as a join or the join table is prequired from a specification, for sake of argument, a string "bcb"?

Edit:

The end result I'm hoping to achieve would be equivalent to this:

SUM(SELECT value * b FROM tableA a JOIN tableB b ON b.id = 1 WHERE a.id = 1,
    SELECT value * c FROM tableA a JOIN tableB b ON b.id = 1 WHERE a.id = 2,
    SELECT value * b FROM tableA a JOIN tableB b ON b.id = 2 WHERE a.id = 3);

I guess there's two parts to this: A simple join of A and selected values from B such that B is reduced to a single selectValue column.

Thanks.

https://stackoverflow.com/questions/7432178/how-can-i-sum-columns-across-multiple-tables-in-mysql — dzm, Nov 06 '17 at 15:55
Please **[EDIT]** your question and add the expected output based on your sample that data. — , Nov 06 '17 at 16:03
I replaced the `psql` tag with `postgresql`. The tag `psql` refers to the default command line client for Postgres, not to the database itself. — , Nov 06 '17 at 16:04
Your question is not very clear... of course you can sum an operation `SELECT SUM(a.value*b.b) FROM "tableA" a JOIN "tableB" b ON a.id=b.id`... but it seems that you want other thing... please be more clear — Dan, Nov 06 '17 at 16:27

Hervé Piedvache · Answer 1 · 2017-11-06T18:22:03.170

1

As asked in comment it should be better to show us what output you really wants, but as I understand you wants to do something like :

SELECT id, SUM(a.value * b.b)
  FROM a JOIN b USING(id)
 GROUP BY id;

It's what you want ? I do not really understand you "bcb" point ...

Not because in your comment you said SUM(value, value, value) and I think you want to add those values so, I'll do something like this :

WITH 
sum1 AS (SELECT value * b AS res 
           FROM tableA a 
           JOIN tableB b ON b.id = 1
          WHERE a.id = 1),
sum2 AS (SELECT value * c AS res 
           FROM tableA a 
           JOIN tableB b ON b.id = 1 
          WHERE a.id = 2),
sum3 AS (SELECT value * b AS res 
           FROM tableA a 
           JOIN tableB b ON b.id = 2 
          WHERE a.id = 3)
SELECT SUM(sum1.res + sum2.res + sum3.res)
  FROM sum1, sum2, sum3;

edited Nov 06 '17 at 18:22

answered Nov 06 '17 at 16:27

Hervé Piedvache

782
4
9

Thanks for the response. I've edited the question, hopefully a bit clearer. – CBusBus Nov 06 '17 at 16:52
@Hervé Piedvache your query returns `NULL`. It does not work because the subqueries return `NULL` or two columns. Your query only works if the subquery returns one number. – Dan Nov 06 '17 at 23:03
You need in this case to add arround each multiplication something like : COALESCE(value * b, 0) AS res this will work. – Hervé Piedvache Nov 07 '17 at 07:45

score 0 · Answer 2 · answered Nov 06 '17 at 22:56

I've tested @Hervé Piedvache's code and it returns NULL, because SELECT value * b AS val FROM tableA a JOIN tableB b ON b.id = 1 WHERE a.id = 1 has two rows. A work around would be:

SELECT SUM(val) FROM
    (SELECT value * b AS val FROM tableA a JOIN tableB b ON b.id = 1 WHERE a.id = 1
    UNION
    SELECT value * c AS val FROM tableA a JOIN tableB b ON b.id = 1 WHERE a.id = 2
    UNION
    SELECT value * b AS val FROM tableA a JOIN tableB b ON b.id = 2 WHERE a.id = 3) data;

Sum query with reference to different coloumn for each row?

2 Answers2