how to sum using sql

Question

is it possible to create a sql that will group entry_ID and entry_ID_1 and sum up the score form score and score"_1?

here is my table

out put come up like this

entry_ID          score
-------------------------
2015              109
2006              54
2010              55

here is my table

activity   entry_ID    score    entry_ID_1    score_1
------------------------------------------------------
4012         2015       55        2006          54
4012         2010       55        2015          54

Images are hard to work with. Could you type (or copy/paste and format) that into your question please? — Schwern, Jan 18 '17 at 02:11
take a look at this post http://stackoverflow.com/questions/12387061/how-to-add-column-values-in-mysql — bos570, Jan 18 '17 at 02:14
In your case it would be something like `SELECT (score + score_1) AS myScore FROM myTable WHERE entry_id =2015` — bos570, Jan 18 '17 at 02:16
Possible duplicate of [How to add column values in mysql](http://stackoverflow.com/questions/12387061/how-to-add-column-values-in-mysql) — alttag, Jan 18 '17 at 02:18
in my case i would like to set them by group and sum the score i tried using this but it sum up all the scores — grey, Jan 18 '17 at 02:23

Phil · Accepted Answer · 2017-01-18T04:16:50.213

1

Something like this should suffice

SELECT a.entry_ID, a.score + COALESCE(b.score_1, 0) AS score
FROM entries a
LEFT JOIN entries b ON a.entry_ID = b.entry_ID_1
UNION
SELECT a.entry_ID_1 AS entry_ID, a.score_1 AS score
FROM entries a
LEFT JOIN entries b ON a.entry_ID_1 = b.entry_ID
WHERE b.entry_ID IS NULL

The second query in the union picks up any entry_ID_1 entries without a corresponding entry_ID entry.

SQLFiddle ~ http://sqlfiddle.com/#!7/4d8ee/3

edited Jan 18 '17 at 04:16

answered Jan 18 '17 at 02:24

Phil

157,677
23
242
245

@grey you should definitely think about normalising your data. Your tables should **not** have columns like `entry_ID/score` **and** `entry_ID_1/score_1` – Phil Jan 18 '17 at 02:38
1

This works for the given case (with only one duplicated `entry_ID` value in `entry_ID_1` column, and no dups in the same column). I doubt the results in the more general case would be desirable. – Disillusioned Jan 18 '17 at 03:47
@CraigYoung good point. Would need some `SUM` and `GROUP BY entry_ID` clauses. I've edited my answer though I have a feeling it's not quite there. Need some better data to work with – Phil Jan 18 '17 at 04:10
Rolled answer back to accepted version. Needs a lot more work to handle duplicate entry IDs in the same column – Phil Jan 18 '17 at 04:17

how to sum using sql

1 Answers1