Sqlite sum all score for a specific name

Question

1.00/5 (1 vote)

See more:

Hello
I need help with summing all values for a specific name in table.

table:

CREATE TABLE Games
(
    id INTEGER PRIMARY KEY,
    teamA TEXT,
    teamB TEXT,
    scoreA INTEGER,
    scoreB INTEGER )};

Insert:

INSERT INTO Games
    (teamA,teamB,scoreA,scoreB)
    VALUES
    ("white","red",10,5),
    ("red","white",18,7),
    ("white","red",6,3);

What i want is to sum white team score with sum().

And thank you in advance.

What I have tried:

i have tried to sum the score for team white, but it always sum the scoreA which is not always for team white as you saw with insert functions.

Posted 1-Mar-17 6:56am

YznIT

Updated 1-Mar-17 11:54am

Sean Ewington

Add a Solution

Comments

Bryian Tan 1-Mar-17 13:25pm

Where is your query? Something like this work?

SELECT teamA, SUM(scoreA) FROM Games
WHERE teamA = 'white'
GROUP BY teamA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-03-01T08:21:00

Solution 1

Try:

SQL

SELECT SUM(CASE WHEN teamA = 'white' THEN scoreA ELSE 0 END + CASE WHEN teamB = 'white' THEN scoreB ELSE 0 END) FROM Games

Posted 1-Mar-17 8:21am

OriginalGriff

Comments

CPallini 1-Mar-17 15:49pm

Nice.

dmjm-h · Answer 2 · 2017-03-01T11:54:00

Your table breaks first normal form.
The columns teamA and teamB are the same column. There is nothing special about teamA; the combination white-red could be red-white without loss of meaning.
Note however that if you were using teamA to indicate home team that also breaks first normal form.
Once you normalize, summing the scores is trivial.

SQL

CREATE TABLE GameScores
(
  id     INTEGER,
  team   TEXT,
  score INTEGER
);
-- Primary key is id + team

INSERT INTO GameScores (id, team, score) 
  VALUES (1, "white", 10), (1, "red", 5),
         (2, "red", 18), (2, "white", 7), 
         (3, "white", 6), (3, "red", 3);

SELECT SUM(score) FROM GameScores WHERE team = "white";

I assume the team ids are for illustration only and that in practice you use neutral ids.