CODEWITHSUNDEEP
sqlsql-serverssmssql-server-2016
Roy
Roy

Reputation: 53

How to get sum of first three rows then next three rows in a new column

hello i have a sql server inner join 

SELECT a.field_name, 
       b.ticker     AS Ticker_Name, 
       Year(c.date) AS YEAR, 
       c.stock_id, 
       c.field_id, 
       Sum(c.value) AS Value 
FROM   field_table a 
       INNER JOIN stock_transaction c 
               ON a.id = c.field_id 
       INNER JOIN stocks b 
               ON b.id = c.stock_id 
WHERE  b.id = 230 
       AND c.field_id = 29 
GROUP  BY Year(c.date), 
          b.ticker, 
          c.stock_id, 
          c.field_id, 
          a.field_name;

I have also attached my output .

enter image description here

my output is sum of sales value year wise . now my task is i have to show sum of value of three rows in a new column . example: 2008 , 2009 , 2010 then again sum of value 2011 , 2012 , 2013 . then again 2014 , 2015 ,2016

my desire output is

enter image description here

can anyone suggest me how can i achieve this .thanks in advance

Upvotes: 1

Views: 1371

Answers (2)

Zhorov
Zhorov

Reputation: 29943

This answer is just a basic example that demonstrates how to sum the values from each three rows as a new column in your result set. You can change this to fit your needs. Example uses ROW_NUMBER() and integer division to calculate each group number.

Input:

Year    Value   
2008    100.00  
2009    200.00  
2010    300.00  
2011    400.00  
2012    500.00  
2013    600.00  
2014    700.00  
2015    800.00  
2016    900.00  
2017    1000.00 
2018    1100.00

Statement:

-- Table
CREATE TABLE #Items (
    [Year] int,
    [Value] numeric(20, 2)
)
INSERT INTO #Items 
    ([Year], [Value])
VALUES
    (2008, 100),
    (2009, 200),
    (2010, 300),
    (2011, 400),
    (2012, 500),
    (2013, 600),
    (2014, 700),
    (2015, 800),
    (2016, 900),
    (2017, 1000),
    (2018, 1100)

-- Statement
;WITH cte AS (
    -- Generate group numbers. Each group is every tree years.
    SELECT 
        [Year], 
        [Value], 
        (ROW_NUMBER() OVER (ORDER BY [Year]) - 1) / 3 AS GroupNumber,
        ROW_NUMBER() OVER (ORDER BY [Year]) AS RowNumber
    FROM #Items
), sums AS (
    -- Get total sums for each group
    SELECT 
        SUM([Value]) AS [SumValue], 
        GroupNumber, 
        ROW_NUMBER() OVER (ORDER BY GroupNumber) AS RowNumber
    FROM cte
    GROUP BY [GroupNumber]
)
-- Final SELECT 
SELECT cte.[Year], cte.[Value], sums.[SumValue]
FROM cte
LEFT JOIN sums ON (cte.RowNumber = sums.RowNumber)

Output:

Year    Value   SumValue
2008    100.00  600.00
2009    200.00  1500.00
2010    300.00  2400.00
2011    400.00  2100.00
2012    500.00  NULL
2013    600.00  NULL
2014    700.00  NULL
2015    800.00  NULL
2016    900.00  NULL
2017    1000.00 NULL
2018    1100.00 NULL

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Do you just want lag()?

SELECT f.field_name, s.ticker AS Ticker_Name, 
       Year(st.date) AS YEAR, st.stock_id, st.field_id, 
       Sum(st.value) AS Value,
       LAG(Sum(st.value, 1)) OVER (PARTITION BY f.field_name, s.ticker, st.stock_id, st.field_id ORDER BY Year(st.date)) as year_1,
       LAG(Sum(st.value, 2)) OVER (PARTITION BY f.field_name, s.ticker, st.stock_id, st.field_id ORDER BY Year(st.date)) as year_2,
       LAG(Sum(st.value, 3)) OVER (PARTITION BY f.field_name, s.ticker, st.stock_id, st.field_id ORDER BY Year(st.date)) as year_3
FROM field_table f INNER JOIN
     stock_transaction st
     ON f.id = st.field_id INNER JOIN
     stocks s 
     ON s.id = st.stock_id 
WHERE s.id = 230 AND st.field_id = 29 
GROUP BY Year(st.date), s.ticker, st.stock_id, st.field_id, f.field_name;

Notice that I also replaced the table aliases with abbreviations for the tables. You should use meaningful table aliases -- such as abbreviations -- rather than arbitrary letters.

Upvotes: 1

Related Questions