mercredi 9 mars 2016

SQL - summing multiple rows with same IDs in different tables

I have 3 tables:

A:
ID | col1 | col2 | have
1  |  x   |   x  |  2
2  |  x   |   y  |  3

B:
ID | col1 | have
1  |  x   |  1
1  |  x   |  5

C:
ID | have
3  | 8
2  | 5
1  | 2

ID is not a primary key, just a foreign key.

I have trouble writing a query which will sum all of them. This is my work and I'm unsure on why it's not working:

SELECT ID, SUM(have)
    FROM (
        SELECT ID, SUM(have) AS have
            FROM A
            GROUP BY ID
        UNION
        SELECT ID, SUM(have) AS have
            FROM B
            GROUP BY ID
        SELECT ID, SUM(have) AS have
            FROM C
            GROUP BY ID
    )
    GROUP BY ID

I'm using sqlite3 in python, but I doubt it would make any difference as this is quite basic SQL query - hence the fault is in data or my query (guessing the latter).

Any ideas?

Aucun commentaire:

Enregistrer un commentaire