Q: Create sum of Elements that are not grouped by I'm trying to sum up all values from the values column by the timestamp "ts".
What I got so far:
select ts, item, value
from statistics
where indicator = "something"

actual:
"1", "item1", "100"
"1", "item2", "200"
"1", "item3", "300"
"2", "item1", "101"
"2", "item2", "202"
"2", "item3", "303"

expected:
"1", "item1", "100"
"1", "item2", "200"
"1", "item3", "300"
"1", "sum", "600"
"2", "item1", "101"
"2", "item2", "202"
"2", "item3", "303"
"2", "sum", "606"


A: Use GROUP BY and WITH ROLLUP.
select ts, item, SUM(value)
from statistics
where indicator = "something"
GROUP BY ts, item WITH ROLLUP

You have to group by both columns to get the original rows in the result, then WITH ROLLUP adds the total lines. This will add a row with item = NULL for the ts totals, and a row at the end with both ts = NULL, item = NULL for the grand total.

A: I think Barmar's solution should be accepted, but just for the sake of giving an alternative solution, I would describe this one:
select ts, item, value
from
(
(
select ts as ts, item, value
from statistics
where indicator = "something"
)
union
(
select ts as ts, 'zzzz_sum' as item, count(*) as value
from statistics
group by ts
)
) t
order by ts, item;

We basically union two result sets and order them accordingly
