So I got two tables
db.execute("""CREATE TABLE persons (
id INTEGER PRIMARY KEY AUTOINCREMENT,
firstname TEXT NOT NULL,
surname TEXT NOT NULL,
UNIQUE (firstname, surname) ON CONFLICT IGNORE
)""")
and
db.execute("""CREATE TABLE scores (
task INTEGER,
idPerson INTEGER,
score INTEGER NOT NULL
)""")
using this select function I can get the following result:
cursor = db.execute("SELECT firstname, surname, uppgift, score FROM persons JOIN scores ON id = idPerson")
('Nils', 'Johansson', 1, 1)
('Maria', 'Johansson', 1, 2)
('Anna', 'Andersson', 1, 0)
('Karl', 'Eriksson', 1, 2)
('Nils', 'Eriksson', 1, 1)
('Anders', 'Eriksson', 1, 0)
.
.
.
('Sven', 'Persson', 100, 0)
('Anders', 'Persson', 100, 2)
('Maria', 'Persson', 100, 1)
('Kristina', 'Persson', 100, 2)
with firstname, surname, task, score.
and with the following function results gets me the sum of the score:
db.execute("SELECT idPerson, SUM(score) FROM scores GROUP BY idPerson ORDER BY idPerson DESC")
(5, 29)
(4, 33)
(3, 19)
(2, 37)
(1, 33)
with id and total score.
what I need to do is in the first result not get the score for each individual task but rather the total score like in the last result. Is this possible?
Aucun commentaire:
Enregistrer un commentaire