Use a regular aggregative function (sum) alongside a window function

Question

I was reading this tutorial on how to calculate running totals.

Copying the suggested approach I have a query of the form:

select
    date,
    sum(sales) over (order by date rows unbounded preceding) as cumulative_sales
from sales_table;

This works fine and does what I want - a running total by date.

However, in addition to the running total, I'd also like to add daily sales:

select
    date,
    sum(sales),
    sum(sales) over (order by date rows unbounded preceding) as cumulative_sales
    from sales_table
group by 1;

This throws an error:

SYNTAX_ERROR: line 6:8: '"sum"("sales") OVER (ORDER BY "activity_date" ASC ROWS UNBOUNDED PRECEDING)' must be an aggregate expression or appear in GROUP BY clause

How can I calculate both daily total as well as running total?

Answer 1

I think you can try it, but it will repeat your daily_sales. In this way you don't need to group by your date field.

SELECT date,
       SUM(sales) OVER (PARTITION BY DATE) as daily_sales
       SUM(sales) OVER (ORDER BY DATE ROWS UNBOUNDED PRECEDING) as cumulative_sales
  FROM sales_table;

Answer 2

Presumably, you intend an aggregation query to begin with:

select date, sum(sales) as daily_sales,
       sum(sum(sales)) over (order by date rows unbounded preceding) as cumulative_sales
from sales_table
group by date
order by date;