CODEWITHSUNDEEP
sqlsql-servert-sql
RalphBiggerton
RalphBiggerton

Reputation: 199

Duplicate data using a partition query

I've written the following partition query in order to show a rolling sum for customer orders over the years, but am getting duplicate records and am not sure what I'm doing wrong here. 

SELECT
    sc.Cust_no,
    YEAR(sc.invoice_date),
    SUM([SQFT]) 
        OVER (PARTITION BY sc.Cust_no, YEAR(sc.invoice_date) 
        ORDER BY YEAR(sc.invoice_date)
     ) as Rolling_Sum
FROM ns.SAR_Combined sc
GROUP BY
    Cust_no,
    sqft,
    YEAR(sc.invoice_date)

What the output looks like

Cust_no Year    Sqft
0001    2015    10806.15
0001    2015    10806.15
0001    2015    10806.15
0001    2015    10806.15
0002    2013    26520
0002    2013    26520
0002    2013    26520
0002    2013    26520
0002    2013    26520

Any help would be appreciated

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I am guessing that you want a cumulative sum. If so, you need to use a window function on the aggregation:

Select sc.Cust_no, year(sc.invoice_date),
       sum(Sum([SQFT])) over (partition by sc.Cust_no, year(sc.invoice_date)
                              order by year(sc.invoice_date)
                             ) as Rolling_Sum
from ns.SAR_Combined sc
group by Cust_no, year(sc.invoice_date);

The reason you are getting "duplication" is due to sqft in the group by clause. However, simply removing it won't fix your problem -- need to change the calculation of Rolling_Sum as well.

Upvotes: 1

Related Questions