How to Count Entries on Same Day and Sum Amount based on the Count?-CodePudding

I am attempting to produce Table2 below - which essentially counts the rows that have the same day and adds up the "amount" column for the rows that are on the same day.

I found a solution online that can count entries from the same day, which works:

SELECT
    DATE_TRUNC('day', datetime) AS date, 
    COUNT(datetime) AS date1
FROM Table1
GROUP BY DATE_TRUNC('day', datetime);

It is partially what I am looking for, but I am having difficulty trying to display all the column names.

In my attempt, I have all the columns I want but the Accumulated Count is not accurate since it counts the rows with unique IDs (because I put "id" in GROUP BY):

SELECT *, count(id) OVER(ORDER BY DateTime) as accumulated_count, 
SUM(Amount) OVER(ORDER BY DateTime) AS Accumulated_Amount 
FROM Table1
GROUP BY date(datetime), id

I've been working on this for days and seemingly have come across every possible outcome that is not what I am looking for. Does anyone have an idea as to what I'm missing here?

CodePudding user response：

Sum and count should be calculated for each day

SELECT
  Table1.*, 
  count(*) over (partition by DATE_TRUNC('day', datetime)) accumulated_count,
  sum(amount) over (partition by DATE_TRUNC('day', datetime)) accumulated_sum
FROM Table1;