I have this table:
| id | area | name | value |
|---|---|---|---|
| 1 | A | abc | 1.2 |
| 2 | B | abc | 2.3 |
| 3 | C | def | 3.4 |
I’m able to calculate the SUM of values for each name:
SELECT
name,
SUM(value) AS "total"
FROM table
GROUP BY name;
and it will output:
| name | total |
|---|---|
| abc | 3.5 |
| def | 3.4 |
Now I want to add these values as a calculated column on the table, my expected output is:
| id | area | name | value | total |
|---|---|---|---|---|
| 1 | A | abc | 1.2 | 3.5 |
| 2 | B | abc | 2.3 | 3.5 |
| 3 | C | def | 3.4 | 3.4 |
My attempt was to add the SELECT clause as column:
SELECT
table.*,
(SELECT
SUM(value) AS "total"
FROM table
GROUP BY name)
FROM table
but it fails:
Subquery returns more than 1 row
I understand why: the SELECT will output all the names, not just the one of the current record. So I added a WHERE clause forcing to select only the current name:
SELECT
id,
area,
name AS current_name,
value,
(SELECT
SUM(value)
FROM table
WHERE name=current_name
GROUP BY name) AS "total"
FROM table
it works!
The downside is the name column is now called current_name.
Is there a way to avoid the use of the alias to keep the actual column name?
You are looking for the window function SUM OVER.
SELECT
t.*,
SUM(value) OVER (PARTITION BY name) AS total
FROM mytable t
A subquery solution would look like this:
SELECT
t.*,
(SELECT SUM(value) FROM mytable t2 WHERE t2.name = t.name) AS total
FROM mytable t
3
try something like this:
SELECT t1.* , t2.total
FROM tabale AS t1
LEFT JOIN ( SELECT
name, SUM(value) AS "total"
FROM table
GROUP BY name ) AS t2 ON t1.name = t2.name;