CODEWITHSUNDEEP
sqlsql-serverssms
nola94
nola94

Reputation: 251

Sum Based on Date

I currently have this code that I want to sum every quantity based on the year. I have written a code that I thought would sum all the charges in 2016 and 2017, but it isn't running correctly.

I added the two different types of partition by statements to test and see if either would work and they don't. When I take them out, the Annual column just shows me the quantity for that specific receipt.

Here is my current code:

SELECT 
  ReceiptNumber
  ,Quantity
  ,Date
  ,sum(CASE WHEN (Date >= '2016-01-01' and Date < '2017-01-01') THEN 
        Quantity 
        ELSE 0 END)
        OVER (PARTITION BY Date) 
        as Annual2016
  ,sum(CASE WHEN (Date >= '2017-01-01' and Date < '2018-01-01') THEN 
        Quantity 
        ELSE 0 END) 
        OVER (PARTITION BY ReceiptNumber)
        as Annual2017
FROM Table1
GROUP BY ReceiptNumber, Quantity, Date

I would like my data to look like this 

ReceiptNumber    Quantity    Date           Annual2016    Annual2017
1                5           2016-01-05     17            13
2                11          2017-04-03     17            13
3                12          2016-11-11     17            13
4                2           2017-09-09     17            13

Here is a sample of some of the data I am pulling from:

ReceiptNumber    Quantity    Date           
1                5           2016-01-05
2                11          2017-04-03
3                12          2016-11-11
4                2           2017-09-09
5                96          2015-07-08
6                15          2016-12-12
7                24          2016-04-19
8                31          2017-01-02
9                10          2017-0404
10               18          2015-10-10
11               56          2017-06-02

Upvotes: 0

Views: 10089

Answers (3)

Carsten Massmann
Carsten Massmann

Reputation: 28236

If you want it printed only once at the top then you should run it in a separate query like:

SELECT YEAR(Date) y, sum(Quantity) s FROM Table1 GROUP BY YEAR(Date)

and then do the main query like this:

SELECT * FROM table1

Easy, peasey ... ;-)

Your original question could also be answered with:

SELECT *, 
 (SELECT SUM(Quantity) FROM Table1 WHERE YEAR(Date)=2016 ) Annual2016,
 (SELECT SUM(Quantity) FROM Table1 WHERE YEAR(Date)=2017 ) Annual2017
FROM table1

Upvotes: 1

dnoeth
dnoeth

Reputation: 60513

You need some conditional aggreation over a Window Aggregate. Simply remove both PARTITION BY as you're already filtering the year in the CASE:

SELECT 
  ReceiptNumber
  ,Quantity
  ,Date
  ,sum(CASE WHEN (Date >= '2016-01-01' and Date < '2017-01-01') THEN 
        Quantity 
        ELSE 0 END)
        OVER () as Annual2016
  ,sum(CASE WHEN (Date >= '2017-01-01' and Date < '2018-01-01') THEN 
        Quantity 
        ELSE 0 END) 
        OVER () as Annual2017
FROM Table1

You probably don't need the final GROUP BY ReceiptNumber, Quantity, Date

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

Try something like this 

Select 
..
sum(CASE WHEN (Date >= '2016-01-01' and Date < '2017-01-01') THEN 
        Quantity 
        ELSE 0 END)
        OVER () as Annual2016
sum(CASE WHEN (Date >= '2017-01-01' and Date < '2018-01-01') THEN 
        Quantity 
        ELSE 0 END)
        OVER ()as Annual2017
.. 
Where Date >= '2016-01-01' and Date < '2018-01-01'

Upvotes: 3

Related Questions