0

I want to calculate the sum and average of my data in the past 24 months how can I achieve that? In my database I have 1000 records with the field (ID, Store_ID, Date, Sales) I want to calculate the average of all my data from THIS month up to the last 24 months.

  • 1
    What attempts have you made at fixing your problem? – Tyler Apr 01 '18 at 23:45
  • have you gone through any tutorials or manuals yet? – Funk Forty Niner Apr 01 '18 at 23:45
  • 1
    you posted this already https://stackoverflow.com/q/49583815/1415724 haven't you? – Funk Forty Niner Apr 01 '18 at 23:46
  • @Tyler Im thinking to sum each month but how can I get the sum of the other past 23 months? –  Apr 01 '18 at 23:48
  • Possible duplicate of [PHP MS SQLSERVER - FOREACH argument](https://stackoverflow.com/questions/49583815/php-ms-sqlserver-foreach-argument) – Tyler Apr 01 '18 at 23:52
  • I'm going to post on your previous question. – Tyler Apr 01 '18 at 23:52
  • @Tyler thats a different question now I want the sql for calculating the sum of this month(April 2018) to the past 23 months can you help me do it? –  Apr 02 '18 at 00:01
  • Possible duplicate of [How does GROUP BY work?](https://stackoverflow.com/questions/7434657/how-does-group-by-work) – Disillusioned Apr 02 '18 at 00:48
  • Even if you have no idea where to start, you could at least put some effort into your question. Read: http://idownvotedbecau.se/noattempt/ Sample data and sample results make your vague descriptions concrete and less ambiguous. Obviously you don't need all 1000 rows. You don't even need 24 months; I'm sure you'd be capable of adapting something that used only the last 2 months. This is not a tutorial site, and no one here gets paid to do your work for you. So, it's extremely disrespectful to put zero effort into your questions. – Disillusioned Apr 02 '18 at 01:00

3 Answers3

0

I believe you are looking for the DATEADD function. Here is the documentation.

Essentially, you will use a where condition to check if a date is older than a certain date.

By using the DATEADD function with the current time, you can create a date X months in the past.

SELECT columns FROM table WHERE date < DATEADD(month, numberOfMonths, GETDATE());

For SQL querys that use aggregate functions (such as SUM), depending on how you use the function you may require the HAVING clause; instead of, the WHERE clause. I don't think your case will require this; but, I may be wrong.

You can read more on HAVING vs WHERE at this article.

Here is an example:

SELECT columns FROM table HAVING date < DATEADD(month, numberOfMonths, GETDATE());

The code samples are untested; however, the general structure is there. We are also using the GETDATE() function to get the current database date.

Tyler
  • 957
  • 11
  • 27
  • one question I want to output this format "Month Name, Year" how can I do this? –  Apr 02 '18 at 00:17
  • @LawrenceAgulto I just remember a possible problem you might run into which can be fixed the `HAVING` clause. I editted my answer. – Tyler Apr 02 '18 at 00:18
  • @LawrenceAgulto Formatting dates should be done in PHP, not the database. Please read up on these two documentations page: [date format](http://php.net/manual/en/datetime.format.php) and [date](http://php.net/manual/en/function.date.php) – Tyler Apr 02 '18 at 00:21
  • You've left out crucial information. So it seems you're guessing and don't really know. This is not helped by you drawing attention to topics that are important but seem irrelevant to OP's problem (which only needs trivial GROUP BY with date manipulation). While your attempt to help is admirable: OP made [no effort](http://idownvotedbecau.se/noattempt/) in the question, so a definitive answer cannot be written. Therefore this Q&A will be of no benefit to other readers. In future, it would be better to get OP to fix the question into something actionable. – Disillusioned Apr 02 '18 at 00:46
0

Please find below SQL where I am getting last 12 month sum of members for a given group in X table.

You can use the similar approach with some modification to below code snippet as per actual table structure to find out desired result set.

;with tbl1
as
(
    select MonthID,count(MemberID) as RecCount from MemberTable a with (nolock) group by MonthID 
)
,tbl2
as
(
    select 
        ROW_NUMBER() OVER(order by MonthID) as RowID
        ,MonthID
        ,sum(RecCount) over(order by MonthID ROWS BETWEEN 11 PRECEDING and CURRENT ROW) as [SumLast12Months] 
    from tbl1   
)
select * from tbl2 where RowID>=12 -- Before RowID 12 none of the month has complete 12 month rollup
PyData
  • 146
  • 1
  • 3
0

Here are a couple of other options:

select sum(Sales) as [Total Sales], avg(Sales) as [Average Sales]
from (select * from @tbl where dt >= dateadd(mm, -24, getdate())) as foo

or

with CTE
as
(
    select * from @tbl where dt >= dateadd(mm, -24, getdate())
)
select sum(Sales) as [Total Sales], avg(Sales) as [Average Sales] from CTE
MichaelB
  • 175
  • 1
  • 10