SQL QUERY - Filter date from the beginning

user_8471914 0
Viewed 92

How to write SQL query that will sum the amount from the previous days/years. Like from the start.

Scenario I want to compute accumulated sales of the store from the day it was opened.

Example

SELECT SUM(AMOUNT) 
FROM TransactionTable 
WHERE TransactionDate = ???

The plan that I have is to query on this table and get the oldest transaction date record, then I will use that in the WHERE condition. Do you think that it is the best solution?

sql-server sql
3 Answers

You can try below using having min(transaction) which will give you the date when transaction first started 

select sum (amt) from
(
  SELECT SUM(AMOUNT) as amt from TransactionTable 
  group by TransactionDate 
  having TransactionDate between min(TransactionDate) and getdate()
)A
user_69148640

To compute accumulated sales of the store from the day it started you can use SUM with OVER clause 

SELECT TransactionDate, SUM(AMOUNT) OVER (ORDER BY TransactionDate) AS AccumulatedSales
FROM TransactionTable
user_105181030

use group by TransactionDate

SELECT convert(date,TransactionDate), SUM(AMOUNT) from TransactionTable 
group by  convert(date,TransactionDate)
user_68085830
Related