CODEWITHSUNDEEP
sqlite
user3617217
user3617217

Reputation: 5

Summing scores from multiple SQLite tables

I have 4 tables as follows:

board1

    round    Nspair   Ewpair   NsPoints    EwPpoints
    1        1        1        4           0
    2        4        3        2           2
    3        3        4        0           4

board2

    round    Nspair   Ewpair   NsPoints        EwPpoints
    1        2        2        2               2
    2        1        4        2               2
    3        4        1        2               2

board3

    round    Nspair   Ewpair   NsPoints        EwPpoints
    1        3        3        3               1
    2        2        1        3               1
    3        1        2        0               4

board4

    round    Nspair   Ewpair   NsPoints        EwPpoints
    1        4        4        0               4
    2        3        2        3               1
    3        2        3        3               1

I would like to create an SQL query to add up each pairs points something like:

SELECT sum NSpoints from Board1, Board2, Board3, Board4 WHERE Nspair = 1

I tried various ways to achieve this but all seem to fall over when the Nspair does not exist as in board 4 above. This looks like it would be a relatively common task and thus there must be a right way to do it. Any help greatly appreciated.

Upvotes: 0

Views: 32

Answers (1)

CL.
CL.

Reputation: 180172

The most correct way to handle this would be a single board table with a board_number column.

Anyway, create a view that can be handled like a table:

CREATE VIEW all_boards AS
SELECT * FROM board1 UNION ALL
SELECT * FROM board2 UNION ALL
SELECT * FROM board3 UNION ALL
SELECT * FROM board4;
...
SELECT SUM(NsPoints) FROM all_boards WHERE Nspair = 1;

Alternatively, combine the tables dynamically:

SELECT SUM(NsPoints)
FROM (SELECT NsPoints, Nspair FROM board1 UNION ALL
      SELECT NsPoints, Nspair FROM board2 UNION ALL
      SELECT NsPoints, Nspair FROM board3 UNION ALL
      SELECT NsPoints, Nspair FROM board4)
WHERE Nspair = 1

Upvotes: 1

Related Questions