How to sum columns for a specific column grouping?-CodePudding

I'm going crazy with what I though tit would be a simple calculation. Essentially I want the sum of a column based on timestamps. I thought with grouping I could solve it but I'm getting a weird behaviour.

Here's the example:

The column in yellow "total category by date" is the output I need.

>   select 
>     day_timestamp,
>     plans,
>     total_employees,
>     sum (total_employees) as total
>     from table

I'm getting crazy numbers instead if the results in the "total category by date" column in my example

Any help how can I accomplish this?

CodePudding user response：

Use GROUP BY

SELECT SUM(total) FROM YOUR_TABLE GROUP BY timestamp

CodePudding user response：

You need to aggregate using a window function, that is, using OVER().

For example:

select 
  timestamp,
  category,
  plans,
  total,
  sum(total) over(partition by timestamp) as total_category_by_date
from t

Result:

 timestamp   category   plans  total  total_category_by_date 
 ----------  ---------- ------ ------ ---------------------- 
 2021-08-26  category1  plan1  100    300                    
 2021-08-26  category2  plan1  200    300                    
 2021-08-27  category1  plan1  100    800                    
 2021-08-27  category2  plan1  200    800                    
 2021-08-27  category3  plan1  500    800

See running example at DB Fiddle.

CodePudding user response：

You can leverage from sub queries and join by using them together.

select category, plans, total, subq.*  from orders 
join (select order_date, sum(total) as order_date_total from orders 
group by order_date) subq on orders.order_date = subq.order_date;

Working fiddle: http://sqlfiddle.com/#!9/59f0a1/11