CODEWITHSUNDEEP
sqlsql-server-2008
user873684
user873684

Reputation: 69

Summing a column by all transactions in a day

I'm trying to sum up all transactions for each day in my database.

SELECT DISTINCT 
    SUM(Balance) OVER (partition by Date) AS account_total, 
    Date 
FROM tbl_FundData 
ORDER BY Date;

The problem with the output is if a transaction is completed at a different time it becomes its own unique sum instead of rolling into the one day. I'm not sure how to modify the query to fix this.

I'm using SQL Server 2008 (I think)

Upvotes: 0

Views: 1499

Answers (4)

Darshil Prajapati
Darshil Prajapati

Reputation: 97

  SELECT SUM(Balance) account_total 
  ,CAST(FLOOR(CAST(IssueDate  AS FLOAT)) AS DATETIME) IssueDate
  FROM tbl_FundData
  GROUP BY
  CAST(FLOOR(CAST(IssueDate  AS FLOAT)) AS DATETIME)
  ORDER BY 
  CAST(FLOOR(CAST(IssueDate  AS FLOAT)) AS DATETIME)

Upvotes: 1

pradip vaghani
pradip vaghani

Reputation: 182

SELECT DISTINCT 
    SUM(Balance) OVER (partition by convert(varchar, Date, 103)) AS account_total,  
    convert(varchar, Date, 103) Date    
FROM tbl_FundData 
ORDER BY  convert(varchar,Date,103)

Upvotes: 1

Mostafa Armandi
Mostafa Armandi

Reputation: 939

Seems yo use DateTime as column data type so cast it as DATE :

SELECT DISTINCT SUM(Balance) OVER (partition by CAST([Date] AS DATE)) AS account_total, CAST([Date] AS DATE)
FROM tbl_FundData 
ORDER BY CAST([Date] AS DATE);

Also you'd better use Group By in this case as :

SELECT SUM(Balance) AS account_total, CAST([Date] AS DATE)
FROM tbl_FundData 
GROUP BY CAST([Date] AS DATE);

Upvotes: 2

President Camacho
President Camacho

Reputation: 1910

I guess you have a timestamp as well in your date that's why you get unique values when you sum. Use this: 

SELECT sum(balance)
FROM tbl_FundData 
GROUP BY convert(date ,date, 106)

106 is a format for date. But you could use whatever.

Upvotes: 0

Related Questions