CODEWITHSUNDEEP
sql
D.Thomson
D.Thomson

Reputation: 1

SQL Query - Sum Quantity for multiple Years

SQL Question - my data has products and quantity sold by fiscal period(1-12) and Fiscal year. The following SQL returns sum Quantity by period for a specific year:

 Select   ABS(Sum(QTY)) Total, FISCPERIOD
    From dbo.ICXLHIS
    where FISCYEAR=2016 and ITEMNUM='LBCNIQTL01100506WCP' and APP = 'PO'
    Group by FISCPERIOD

I want to have 2016 and 2017 data however when I add 2017 to the Where clause it returns incorrect values for the sum quantities:

Select   ABS(Sum(QTY)) Total, FISCPERIOD, FISCYEAR
From dbo.ICXLHIS
where FISCYEAR=2016 and FISCYEAR=2017 and ITEMNUM='LBCNIQTL01100506WCP' and 
APP = 'PO'
Group by FISCPERIOD, FISCYEAR

Not sure what I am doing wrong

Upvotes: 0

Views: 173

Answers (2)

Robson Martins
Robson Martins

Reputation: 11

maybe it's the problem where FISCYEAR=2016 and FISCYEAR=2017

try to do this where FISCYEAR between (2016 and 2017)

I hope have helped you!

Upvotes: 0

mauro
mauro

Reputation: 5940

I guess FISCALYEAR cannot be both 2016 and 2017. You might want to change your SQL this way:

Select   ABS(Sum(QTY)) Total, FISCPERIOD, FISCYEAR
From dbo.ICXLHIS
where FISCYEAR IN (2016, 2017) and 
      ITEMNUM='LBCNIQTL01100506WCP' and 
      APP = 'PO'
Group by FISCPERIOD, FISCYEAR

Upvotes: 1

Related Questions