sum across a row in sql

Question

I want to sum the values of Punten1,Punten 2,Punten 3,Punten4 as a total. But I only know how to sum the values across the column. But not across the row. Does anyone know how to do this.

SELECT Punten1, Punten2, Punten3, Punten4
Punten1 + Punten 2 + Punten3 + Punten4 as Puntentotaal
FROM puntentotaal

So i would like to sum the values of Punten1,Punten 2,Punten 3,Punten4 in a new column: puntentotaal thanks!

Solution 1

SELECT Punten1, Punten2, Punten3, Punten4, 
Punten1 + Punten2 + Punten3 + Punten4 as Puntentotaal
FROM puntentotaal;

just an extra comma. I am assuming your table as unique ID as a column.

UPDATE puntentotaal AS t1 INNER JOIN puntentotaal AS t2 SET t1.Puntentotaal = 
(t2.Punten1 + t2.Punten2 + t2.Punten3 + t2.Punten4) where t1.ID = t2.ID;

This will update the sum of Punten1,Punten 2,Punten 3,Punten4 in column Puntentotaal in all the rows.

Solution 2

Your question titles as "sum across row" but what you are trying to achieve is sum across column.

If you are looking for "sum across row" there is function called SUM(field_name) which will give you sum of specific field for all the rows selected, you can use this function with GROUP BY

If you are looking for "sum across column" you are doing it right with + sign, just need to add comma as per @paul suggested.

Comments

[Strawberry]

? But that IS exactly how you do it!?!? Although this kind of query is often indicative of poor table design!

[Minesh]

your question titles as "sum across row" but what you are trying to achieve is sum across column.

[warnerst]

But now it is a temporary column. But what do I need to change if i want a new column in the table. Where the new values will stay?

[Govil]

add column in your table. ALTER TABLE puntentotaal ADD COLUMN Punten5 INTEGER; It is little unclear what do you want to achieve with new column.

[warnerst]

Punten means points and puntentotaal means Totalpoints. So i am summing the points and I want the total in a new column. But not a temporary.

[Govil]

okay, you can try this. I am assuming your table as unique ID as a column. UPDATE puntentotaal AS t1 INNER JOIN puntentotaal AS t2 SET t1.Puntentotaal = (t2.Punten1 + t2.Punten2 + t2.Punten3 + t2.Punten4) where t1.ID = t2.ID;

[Govil]

this will update all the total sum points of Punten1 + Punten2 + Punten3 + Punten4 in Puntentotaal column.

[Andriy M]

You don't need a self-join in your UPDATE statement. UPDATE puntentotaal SET Puntentotaal = (Punten1 + Punten2 + Punten3 + Punten4) should be just enough.